How to create fast database queries

Oracle: generating a list of dates and counting ranges for each date

with 10 comments

From Stack Overflow:

I have a table with data such as below:

Group Start Date End Date
A 2001.01.01 2001.01.03
A 2001.01.01 2001.01.02
A 2001.01.03 2001.01.03
B 2001.01.01 2001.01.01

I am looking to produce a view that gives a count for each day

We have a list of ranges here, and for each date we should count the number of ranges that contain this date.

To make this query, we will employ one simple fact: the number of ranges containing a given date is the number of ranges started on or before this date minus the number of ranges that ended before this date.

This can easily be calculated using window functions.

Let's create a sample table:

Table creation details

Written by Quassnoi

September 9th, 2009 at 11:00 pm

Posted in Oracle

10 Responses to 'Oracle: generating a list of dates and counting ranges for each date'

Subscribe to comments with RSS

  1. Thanks for this comprehensive article. However, when I test your SQL with

    INWORK 01/FEB/13 02/FEB/13
    INWORK 01/FEB/13 02/FEB/13
    RELEASE 04/FEB/13 06/FEB/13
    REVIEW 02/FEB/13 04/FEB/13

    I get

    01/FEB/13 INWORK 1
    02/FEB/13 INWORK 1
    03/FEB/13 INWORK -1
    04/FEB/13 INWORK -1
    05/FEB/13 INWORK -1
    06/FEB/13 INWORK -1
    01/FEB/13 RELEASE 0
    02/FEB/13 RELEASE 0
    03/FEB/13 RELEASE 0
    04/FEB/13 RELEASE 1
    05/FEB/13 RELEASE 1
    06/FEB/13 RELEASE 1
    01/FEB/13 REVIEW 0
    02/FEB/13 REVIEW 1
    03/FEB/13 REVIEW 1
    04/FEB/13 REVIEW 1
    05/FEB/13 REVIEW 0
    06/FEB/13 REVIEW 0

    Which does not seem correct ie : how can there be negative counts.

    Any suggestions on what I am doing wrong?


    20 Feb 13 at 06:20

  2. @Richard: please post your sample data and the query on http://sqlfiddle.com and post here the link.


    20 Feb 13 at 12:06

  3. Quassnoi – Thanks for the assistance. I have posted the code at http://sqlfiddle.com/#!4/e6bf9/1/0

    As background, the data represents the approval work flow of two objects. One object that goes from INWORK to REVIEW to RELEASE during the dates between 01FEB13 to 08FEB13; the second object is INWORK from 01FEB13 to 08FEB13.

    The output I require is, for end (23:59:59) of each day, the number of objects in the INWORK state, the number in REVIEW state and the number in RELEASE state.

    The problem I have is that the output for 02FEB13, on summing the counts has a total of 3. It should be 2. Similarly for 04FEB13, the total is 3, not 2.

    If I add the HH:MI:SS values to further descretize the intervals, the output is more incorrect. see http://sqlfiddle.com/#!4/34aaa/1/0

    Any suggestions on how to ensure the counts equal 2 for each day I count?


    20 Feb 13 at 13:26

  4. @Richard: I don’t see the number 3 in the output at all here: http://sqlfiddle.com/#!4/e6bf9/1/0

    You have 2 inwork tasks between Feb 1 and Feb 2; and 1 inwork task for all other dates. The other counts also seem legit to me.


    20 Feb 13 at 13:34

  5. Thanks for your response – the total for 02FEB13 is 2 for INWORK, and 1 for REVIEW, total of 3, not 2. This is the problem :-)


    20 Feb 13 at 13:56

  6. @Richard: if you need the totals not regarding the task state, just omit the whole “grouper” stuff. See here: http://sqlfiddle.com/#!4/d1ef5/5

    Please note that you have overlapping dates for some of your states so those states will be counted twice. You model does not allow distinguishing the tasks since you don’t keep the task id in your table.


    20 Feb 13 at 14:00

  7. As you can see from my second SQL example, if I include hh:mm:ss then there is not overlap of the work flow increments.

    Also, the next part of this problem is that I use the output as SQL for a stacked bar chart in iReport, thus the grouper field is used as the series in the stacked bar chart. So the total of the bar charts should be 2. Note also that I am reporting at 23:59:59 of each day. Thus for the 02 FEB13 I should not count the INWORK task that is completed.


    20 Feb 13 at 14:12

  8. @Richard: as I said, there is no way to distinguish the tasks. Add task id to your model.


    20 Feb 13 at 14:49

  9. Quassnoi – as per the sample @ http://sqlfiddle.com/#!4/d1ef5/5 the inclusion of a unique identifier for each work flow does not solve the problem. I still see 3 counts for 02FB13 and 04FEB13. There should only be 2.


    21 Feb 13 at 01:20

  10. Hi @quassnoi I found this article similar with my problem,
    can you please also help me here https://stackoverflow.com/questions/59319542/count-data-by-loop-calendar-sql-oracle

    thank you


    13 Dec 19 at 13:28

Leave a Reply