We help IT Professionals succeed at work.

Break date range into a new row

cutie_smily
cutie_smily used Ask the Experts™
on
Here is my requirement. Can we achieve this in SQL?

TableA:
0123400000,11/01/2008,04/29/2009
0123400000,04/30/2009,07/14/2010
0123400000,07/15/2010,07/16/2010
0123400000,07/17/2010,09/17/2010

TableB:
0123400000 01/01/2010,12/31/2021

select a.col1, a.col2, a.col3, b.col1, b.col2,b.col3
from tablea
left join tableB
on tabla.col1 = tablb.col1
and a.col2<= b.col3
and a.col3 >= b.col2

Result:
0123400000,11/01/2008,04/29/ 2009,0123400000,NULL,,NULL
0123400000,04/30/2009,07/14/2010,0123400000,01/01/ 2010,12/31/2021
0123400000,07/15/2010,07/16/2010,0123400000,01/01/ 2010,12/31/2021
0123400000,07/17/2010,09/17/2010,0123400000,01/01/ 2010,12/31/2021

Requirement:
I have to create an additional row when there is match with tableb and a.col2 and b.col2 are not aligned correctly and also have to adjust the dates when I get the first match from tableB

Required Output:

Result:
0123400000,11/01/2008,04/29/2009
0123400000,04/30/2009,12/31/2009 --- adjust the end date to b.col2-1
0123400000,01/01/2010,07/14/2010 ---- create this additional row and have the same b.col2 date.
0123400000,07/15/2010,07/16/2010
0123400000,07/17/2010,09/17/2010
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Production Manager / Application Support Manager
Commented:
You can write a pl/sql procedure to do this for you. It may not be easy to do this with a single sql statement straight away.

Thanks,
>> there is match with tableb and a.col2 and b.col2
As per the data provided I dont see any match on a.col2 and b.col2
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> and a.col2 and b.col2 are not aligned correctly

can you explain what "aligned correctly" means?
Metehan OzculluSQL Service Manager
Commented:
select a.col1, a.col2, a.col3
from tablea a
left join tableB b
on a.col1 = b.col1
and a.col2<= b.col3
and a.col3 >= b.col2
where  b.col2 is null

union

select a.col1, a.col2, a.col3
from tablea a
left join tableB b
on a.col1 = b.col1
and a.col2<= b.col3
and a.col3 >= b.col2
where a.col2 > b.col2

union

select a.col1, a.col2, DATEADD(day,-1,b.col2)
from tablea a
left join tableB b
on a.col1 = b.col1
and a.col2<= b.col3
and a.col3 >= b.col2
where a.col2 < b.col2

union

select a.col1, b.col2, a.col3
from tablea a
left join tableB b
on a.col1 = b.col1
and a.col2<= b.col3
and a.col3 >= b.col2
where a.col2 < b.col2

Author

Commented:
what I meant by "aligned correctly"

1) When the dates on table A and tableB start at the same period then I don't need a additional record until the end of the tableB.col3

Here is a data sample where I do NOT need a row
 TableA
2010-01-01 2010-05-31
2010-06-01 2010-12-31

TableB
2010-01-01 2010-05-31
2010-06-01 2010-12-31
2011-01-01 2011-12-31

Here is a data sample where I need an additional row:
Table A
04/30/2009 02/11/2010
02/12/2010 12/31/2010
tableB
05/02/2009 07/15/2010

Result:
04/30/2009 05/01/2009 -- Adjust end date
05/02/2009 02/11/2010 --insert new row
02/12/2010 07/15/2010 --adjust end date
07/16/2010 12/31/2010 -- adjust begin date.

Please let me know if you have any questions
If you got your issue resolved you can close this question or else you can provide the answers for the experts asked you.

Author

Commented:
I do not have the solution yet. One of my colleague has tried doing this using the calendar table and he said he will forward the final query when he thinks it is complete.