Solved

splitting into multiple rows oracle

Posted on 2014-03-13
7
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 48

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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 48

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 48

Accepted Solution

by:
PortletPaul earned 500 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 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