Solved

splitting into multiple rows oracle

Posted on 2014-03-13
7
238 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 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
Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

 
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 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

'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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

691 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