split a record by date span 1

[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


i need a SQL solution.

any help is appreciated.

 

[al1024]

You could use calendar view :

select  bat.batch_id
    ,   cal.calendar_date   as start_date
    ,   case when cal.calendar_date + 30 >= bat.end_date 
                then bat.end_date 
            else cal.calendar_date + 30 
        end as end_date
from    batch   as bat
    inner join
        sys_calendar.calendar   as cal
        on  (cal.calendar_date - bat.start_date + 1) mod 30 = 0
        and cal.calendar_date between bat.start_date and bat.end_date
;

 

[Jumroo]

Thanks al1024!

my solution was very very close to what you suggested.