split ONE record into multiple lines 7

[Jumroo]

can someone help me to split ONE record into multiple lines with the same batch_id if the service start date and service end date are more than 30 days apart.

For e.g the current data is in the format below
batch_id service start date service end date
99999 2/15/2008 4/25/2008

Need to be transformed to

batch_id service start date service end date
99999 2/15/2008 3/16/2008
99999 3/16/2008 4/15/2008
99999 4/15/2008 4/25/2008

 

[SantaMufasa]

Jumroo,

To produce more rows of output than exist in your source table, it is often useful to use some "dummy table" with plenty of rows to actually seed the query with as many rows as you want to produce.

For the sake of conceptual simplicity, I choose as my driving "dummy table" one that I know exists in every Oracle database: "ALL_TAB_COLUMNS". The only thing that I use from "ALL_TAB_COLUMNS" is row numbers. In your case, I want printed rows for each 30-day period between your "service_start_date" and your "service_end_date" values for a row.

Since Oracle cannot anticipate how many result rows you need for each source row (i.e., one row for each 30-day period between a row's "service_start_date" and "service_end_date"), we must query enough rows from "ALL_TAB_COLUMNS" to accommodate the largest number of rows our data prescribes, yet we do not want to degrade performance by reading all rows from "ALL_TAB_COLUMNS" (since each Oracle database usually contains tens of thousands of rows in it "ALL_TAB_COLUMNS"). Therefore, In the code, below, I arbitrarily set a limit of 120 (i.e., roughly ten-years worth of) rows to be available to drive your query. If you need more rows or fewer rows, you can easily adjust the 120 to a more appropriate value.

Also, in the code, below, I added an extra column that you did not specify, "Service Step within Batch", which identifies the "month-step" of the row within the current batch. You are certainly welcome to remove that if you wish...I just put it there for your benefit.

I also added more test rows to illustrate:[ul][li]a "multi-year batch" and[/li][li]a batch lasting less than 30 days[/li][/ul]

Here are data and code to do what you wanted:

select * from services;

  BATCH_ID SERVICE_S SERVICE_E
---------- --------- ---------
     99999 15-FEB-08 25-APR-08
     11111 22-JAN-06 04-FEB-08
     22222 12-JAN-08 14-JAN-08

col month_num heading "Service|Step|within|Batch" format 999
col service_start_date heading "Service|Start|Date"
col service_end_date heading "Service|End|Date"
select month_num
      ,x.batch_id
      ,x.service_start_date+decode(month_num,1,0,(30*(month_num-1))+1) service_start_date
      ,least(x.service_start_date+(30*(month_num)),x.service_end_date) service_end_date
  from services x
      ,(select rownum month_num from all_tab_columns where rownum <= 120)
 where month_num <= ceil(months_between(service_end_date,service_start_date))
 order by batch_id,month_num
/

Service
   Step            Service   Service
 within            Start     End
  Batch   BATCH_ID Date      Date
------- ---------- --------- ---------
      1      11111 22-JAN-06 21-FEB-06
      2      11111 22-FEB-06 23-MAR-06
      3      11111 24-MAR-06 22-APR-06
      4      11111 23-APR-06 22-MAY-06
      5      11111 23-MAY-06 21-JUN-06
      6      11111 22-JUN-06 21-JUL-06
      7      11111 22-JUL-06 20-AUG-06
      8      11111 21-AUG-06 19-SEP-06
      9      11111 20-SEP-06 19-OCT-06
     10      11111 20-OCT-06 18-NOV-06
     11      11111 19-NOV-06 18-DEC-06
     12      11111 19-DEC-06 17-JAN-07
     13      11111 18-JAN-07 16-FEB-07
     14      11111 17-FEB-07 18-MAR-07
     15      11111 19-MAR-07 17-APR-07
     16      11111 18-APR-07 17-MAY-07
     17      11111 18-MAY-07 16-JUN-07
     18      11111 17-JUN-07 16-JUL-07
     19      11111 17-JUL-07 15-AUG-07
     20      11111 16-AUG-07 14-SEP-07
     21      11111 15-SEP-07 14-OCT-07
     22      11111 15-OCT-07 13-NOV-07
     23      11111 14-NOV-07 13-DEC-07
     24      11111 14-DEC-07 12-JAN-08
     25      11111 13-JAN-08 04-FEB-08
      1      22222 12-JAN-08 14-JAN-08
      1      99999 15-FEB-08 16-MAR-08
      2      99999 17-MAR-08 15-APR-08
      3      99999 16-APR-08 25-APR-08

Let us know if you have questions about any of the code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Hi Dave,
can I suggest that

select rownum month_num from all_tab_columns where rownum <= 120

in a v11g Database would be a poor use of resources to generate a few rows. Much more efficient to use

SELECT LEVEL
FROM dual
CONNECT BY LEVEL <=120

Also valid for 10g but a bit buggy in 9i (if I recall)

 

[SantaMufasa]

Jim, Excellent solution. And, yes, you are correct...the code absolutely, positively does not work on Oracle versions prior to 10g (and my default, laptop DB is still a 9i version), thus my earlier solution.

When responding to question in the 10g/11g forums that can benefit from "dummy rows" to drive a query, I'll be certain to use the "...CONNECT BY..." option.

In the meantime, hava

for changing my paradigm.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Jumroo]

Thank you!!! Santamufasa. your solution worked great!!

and thanks to Jimirvine for his suggestions too.

 

[jimirvine]

Dave, as always, the level of asssistance that you provide users here at tek-tips goes above and beyond. I'm just glad to stick my nose in every now and again to offer a wee bit of a different view.
Jumroo, it is customary on tis forum, that when someone assists you, you click on the 'Thank [user] for this valuable post' link. I'm sure that you would want to thank Dave n this way for his, as always, excellent advice.

 

[Jumroo]

Thanks for showing me how to appreciate it the tek-tip way!!!

all the altruism is appreciated!! this is what makes the coding worl go around!

 

[taupirho]

A small change to get Jims "dual" code to work in V9

select lv from
(
SELECT LEVEL lv
FROM dual
CONNECT BY LEVEL <=120
)



In order to understand recursion, you must first understand recursion.

 

[jimirvine]

Cheers Taupirho, have a star (although I think that it should have read:
A small change to get "Jims" dual code to work in V9