?
Solved

splitting into multiple rows oracle

Posted on 2014-03-13
7
Medium Priority
?
241 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
Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

770 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