asked on

splitting into multiple rows oracle

Dear experts -

Following is the data. if the record is ACT, enddate is not null, the record has to repeated till the enddate. if the enddate is null, the considered expired after 30 days, so it has to be repeated 30 times.. thanks for your support as always...

id, startdate, enddate, statuscode

123 12/01/2013 12/03/2013 ACT
234 12/02/2013 12/05/2013 ACT
456 12/01/2013 ACT

what I was asked is, I need to create data like

id startdate
123 12/01/2013
123 12/02/2013
123 12/03/2013
234 12/02/2013
234 12/03/2013
234 12/04/2013
234 12/05/2013
456 12/01/2013
456 12/02/2013
...
...
..
456 12/30/2013

thanks

PortletPaul

You have both Oracle and PostgreSQL as tags.

Which one actually applies? (the syntax will differ)

akp007

ASKER

postgres

thanks

akp007

ASKER

if you could give us some kind of direction , of how to implement or some design, that would be great...

Regards

PortletPaul

just a quick note:

do you have a table of dates (one row per day)?

if so use this, or use a recursive common table expression to build one dynamically.

Once you have the needed range of dates in a table or CTE then join you data table to the dates (that's right the dates first, left join your data table to this)

the join will use "between" and this will join each day to a record in your existing table

have to run right now, sorry.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

akp007

ASKER

many thanks..