?
Solved

splitting into multiple rows oracle

Posted on 2014-03-13
7
Medium Priority
?
249 Views
Last Modified: 2014-03-17
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
0
Comment
Question by:akp007
  • 3
  • 3
6 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39928082
You have both Oracle and PostgreSQL as tags.

Which one actually applies? (the syntax will differ)
0
 

Author Comment

by:akp007
ID: 39928180
postgres

thanks
0
 

Author Comment

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

Regards
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39928505
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.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39930805
Here is a working example of this in PostgreSQL 9.3.1:
    CREATE TABLE Table1
    	("id" int, "startdate" timestamp, "enddate" timestamp, "statuscode" varchar(3))
    ;
    	
    INSERT INTO Table1
    	("id", "startdate", "enddate", "statuscode")
    VALUES
    	(123, '2013-12-01 00:00:00', '12/03/2013', 'ACT'),
    	(234, '2013-12-02 00:00:00', '12/05/2013', 'ACT'),
    	(456, '2013-12-01 00:00:00', NULL, 'ACT')
    ;

**Query 1**:

    WITH RECURSIVE dts(dt) AS (
    
        SELECT min(startdate)
        FROM Table1
      UNION ALL
        SELECT dt + interval '1 day'
        FROM dts
        WHERE dt < (SELECT max(coalesce(enddate,startdate + interval '30 day')) FROM Table1)
      )
    
    SELECT
          id
        , dt AS startdate
        , statuscode
    FROM dts
    JOIN table1
      ON dts.dt BETWEEN table1.startdate AND coalesce(enddate,startdate + interval '30 day')
    ORDER BY
          id
        , dt
    

**[Results][2]**:
    
    |  ID |                       STARTDATE | STATUSCODE |
    |-----|---------------------------------|------------|
    | 123 | December, 01 2013 00:00:00+0000 |        ACT |
    | 123 | December, 02 2013 00:00:00+0000 |        ACT |
    | 123 | December, 03 2013 00:00:00+0000 |        ACT |
    | 234 | December, 02 2013 00:00:00+0000 |        ACT |
    | 234 | December, 03 2013 00:00:00+0000 |        ACT |
    | 234 | December, 04 2013 00:00:00+0000 |        ACT |
    | 234 | December, 05 2013 00:00:00+0000 |        ACT |
    | 456 | December, 01 2013 00:00:00+0000 |        ACT |
    | 456 | December, 02 2013 00:00:00+0000 |        ACT |
    | 456 | December, 03 2013 00:00:00+0000 |        ACT |
    | 456 | December, 04 2013 00:00:00+0000 |        ACT |
    | 456 | December, 05 2013 00:00:00+0000 |        ACT |
    | 456 | December, 06 2013 00:00:00+0000 |        ACT |
    | 456 | December, 07 2013 00:00:00+0000 |        ACT |
    | 456 | December, 08 2013 00:00:00+0000 |        ACT |
    | 456 | December, 09 2013 00:00:00+0000 |        ACT |
    | 456 | December, 10 2013 00:00:00+0000 |        ACT |
    | 456 | December, 11 2013 00:00:00+0000 |        ACT |
    | 456 | December, 12 2013 00:00:00+0000 |        ACT |
    | 456 | December, 13 2013 00:00:00+0000 |        ACT |
    | 456 | December, 14 2013 00:00:00+0000 |        ACT |
    | 456 | December, 15 2013 00:00:00+0000 |        ACT |
    | 456 | December, 16 2013 00:00:00+0000 |        ACT |
    | 456 | December, 17 2013 00:00:00+0000 |        ACT |
    | 456 | December, 18 2013 00:00:00+0000 |        ACT |
    | 456 | December, 19 2013 00:00:00+0000 |        ACT |
    | 456 | December, 20 2013 00:00:00+0000 |        ACT |
    | 456 | December, 21 2013 00:00:00+0000 |        ACT |
    | 456 | December, 22 2013 00:00:00+0000 |        ACT |
    | 456 | December, 23 2013 00:00:00+0000 |        ACT |
    | 456 | December, 24 2013 00:00:00+0000 |        ACT |
    | 456 | December, 25 2013 00:00:00+0000 |        ACT |
    | 456 | December, 26 2013 00:00:00+0000 |        ACT |
    | 456 | December, 27 2013 00:00:00+0000 |        ACT |
    | 456 | December, 28 2013 00:00:00+0000 |        ACT |
    | 456 | December, 29 2013 00:00:00+0000 |        ACT |
    | 456 | December, 30 2013 00:00:00+0000 |        ACT |
    | 456 | December, 31 2013 00:00:00+0000 |        ACT |



  [1]: http://sqlfiddle.com/#!15/e9166/9

  [2]: http://sqlfiddle.com/#!15/e9166/9/0

Open in new window

0
 

Author Closing Comment

by:akp007
ID: 39934838
many thanks..
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question