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
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
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.
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.
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_DATE – DL.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.