CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Vivek
Vivek

Reputation: 39

How to expand records based on start and end date in SQL or PL/SQL

My Source Looks like this

ID    Name      Start_Date   End_ate
1     John      3-Aug-2017   30-Nov-2017
2     Nikhil    1-Jul-2017   31-Oct-2017

I want to expand these records based on start_Date and End_date i.e. For every entry in the above source table, return a separate DATE_ID for every day between START_DATE and END_DATE so something like this

ID     Name    Date_ID
1      John    3-Aug-2017
1      John    4-Aug-2017
1      John    5-Aug-2017
.
.
.
1     John    30-Nov-2017
2     Nikhil  1-Jul-2017
2     Nikhil  2-Jul-2017
.
.
.
2    Nikhil  31-Oct-2017

Since start and end date for each record is not fixed so I am not sure how to implement this.

Thanks in Advance.

Upvotes: 1

Views: 559

Answers (1)

Nexus
Nexus

Reputation: 821

Here you are, replace [a] with [your_table_name] :

SELECT (a.Start_Date + ROWNUM - 1) Date_ID, a.ID, a.Name
  FROM all_objects, a
 WHERE ROWNUM <= TO_DATE(a.End_Date, 'dd-mon-yyyy') -
       TO_DATE(a.Start_Date, 'dd-mon-yyyy') + 1;

Upvotes: 2

Related Questions