troubleshooting Question

Break date range into a new row

Avatar of cutie_smily
cutie_smily asked on
Oracle DatabaseSQL
7 Comments2 Solutions463 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros