Date range split into one row for each date

Consider a scenario when you have a table which has data for some date range. Each record in the table has a date range stored in two columns i.e. from_date and to_date along with some other columns. Requirement is to split each row into as many rows as the number of days between the from_date and to_date columns.

Data Example for date range

Id From_date To_date
1 01-JAN-2017 02-JAN-2017
2 05-JAN-2017 07-JAN-2017
3 10-JAN-2017 10-JAN-2017

DATE_LOG table

Above data example shows a table were each row stores a date range. First row has a date range of 2 days from 1st Jan to 2nd Jan 2017. Second row has a date range of 3 days from 5th Jan to 7th Jan 2017 and so on. There can be any number of other columns also, but for simplicity sake here we have just one other column ID. we need to get an output like shown below

Id From_date To_date Day
1 01-JAN-2017 02-JAN-2017 01-jan-2017
1 01-JAN-2017 02-JAN-2017 02-JAN-2017
2 05-JAN-2017 07-JAN-2017 05-JAN-2017
2 05-JAN-2017 07-JAN-2017 06-JAN-2017
2 05-JAN-2017 07-JAN-2017 07-JAN-2017
3 10-JAN-2017 10-JAN-2017 10-JAN-2017

Expected Output after splitting date range into one row for each date

Here it’s easy to know how many rows each row should be split into by calculating number of days between from_date and to_date but challenge is to split the rows into that many days using SQL.

To achieve this we’ll need another helper table. This table will store just numbers as shown below.

INT_TAB

INT
0
1
2
3
4

…………………………..

…………………………..

Above concept of using integer table and way to create and populate it has been very well explained in below blog. Please do take a look.

Integer Table

Solution

Using integer table below query can be used to get the expected output.

SQL QUERY FOR ORACLE

SELECT DL.ID,DL.FROM_DATE,DL.TO_DATE,DL.FROM_DATE + IT.INT             FROM DATE_LOG DL,                                                               INT_TAB IT                                                       WHERE TO_NUMBER(DL.TO_DATEDL.FROM_DATE) >= IT.INT                       ORDER BY DL.ID,DL.FROM_DATE + IT.INT

Output of this query is as shown in the expected output above.

Leave a Reply

Your email address will not be published. Required fields are marked *