Getting the days between two dates for multiple IDs

Question

This is a follow up question from How to get a list of months between 2 given dates using a query? really. (I suspect it's because I don't quite understand the logic behind connect by level clauses !)

What I have is a list of data like so

ID | START_DATE  | END_DATE
 1 | 01-JAN-2018 | 20-JAN-2018
 2 | 13-FEB-2018 | 20-MAR-2018
 3 | 01-MAR-2018 | 07-MAR-2018

and what I want to try and get is a list with all the days between the start and end date for each ID.

So for example I want a list which gives

ID | DATE
 1 | 01-JAN-2018
 1 | 02-JAN-2018
 1 | 03-JAN-2018 
...
 1 | 19-JAN-2018
 1 | 20_JAN-2018
 2 | 13-FEB-2018
 2 | 14-FEB-2018
 2 | 15-FEB-2018 
...

etc.

What I've tried to do is adapt one of the answers from the above link as follows

select id
, trunc(start_date+((level-1)),'DD') 
from (
  select id
  , start_date
  , end_date
  from blah
 ) 
connect by level <= ((trunc(end_date,'DD')-trunc(start_date,'DD'))) + 1

which gives me what I want but then a whole host of duplicate dates as if it's like a cartesian join. Is there something simple I need to add to fix this?

That's because it is a cartesian join - you aren't restricting based on id at all, so it tries generating rows based on all previous "starting" (original) rows. As a side note: your data shows an inclusive upper-bound on your desired results - it's much better, long-term, to use an exclusive upper-bound (and note the accepted uses an exclusive one as well). This is especially important in Oracle due to the `DATE` type also including the time as well. — Clockwork-Muse, Jul 04 '18 at 15:49

score 2 · Accepted Answer · answered Jul 04 '18 at 15:18

2

I like recursive CTEs:

with cte as (
      select id, start_dte as dte, end_dte
      from blah
      union all
      select id, dte + 1, end_dte
      from cte
      where dte < end_dte
     )
select *
from cte
order by id, dte;

This is ANSI standard syntax and works in several other databases.

answered Jul 04 '18 at 15:18

Gordon Linoff

1,242,037
58
646
786

Hi Gordon, Thanks alot - I've never used a recursive CTE before. Geez there's so many SQL tricks I need to learn :) – C. Coles Jul 04 '18 at 15:28

score 0 · Answer 2 · answered Jul 04 '18 at 16:14

The hierarchical query you were trying to do needs to include id = prior id in the connect-by clause, but as that causes loops with multiple source rows you also need to include a call to a non-deterministic function, such as dbms_random.value:

select id, start_date + level - 1 as day
from blah
connect by level <= end_date - start_date + 1
and prior id = id
and prior dbms_random.value is not null

With your sample data in a CTE, that gets 63 rows back:

with blah (ID, START_DATE, END_DATE) as (
            select 1, date '2018-01-01', date '2018-01-20' from dual
  union all select 2, date '2018-02-13', date '2018-03-20' from dual
  union all select 3, date '2018-03-01', date '2018-03-07' from dual
)
select id, start_date + level - 1 as day
from blah
connect by level <= end_date - start_date + 1
and prior id = id
and prior dbms_random.value is not null;

        ID DAY       
---------- ----------
         1 2018-01-01
         1 2018-01-02
         1 2018-01-03
...
         1 2018-01-19
         1 2018-01-20
         2 2018-02-13
         2 2018-02-14
...
         3 2018-03-05
         3 2018-03-06
         3 2018-03-07

You don't need to trunc() the dates unless they have non-midnight times, which seems unlikely in this case, and even then it might not be necessary if only the end-date has a later time (like 23:59:59).

A recursive CTE is more intuitive in many ways though, at least once you understand the basic idea of them; so I'd probably use Gordon's approach too. There can be differences in performance and whether they work at all for large amounts of data (or generated rows), but for a lot of data it's worth comparing different approaches to find the most suitable/performant anyway.

Thank you very much Alex, it's really helpful having an example I can come back to and practice against! — C. Coles, Jul 04 '18 at 16:20

Getting the days between two dates for multiple IDs

2 Answers2