[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

splitting into multiple rows oracle

Posted on 2014-03-13
7
Medium Priority
?
244 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

649 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