Avatar of cutie_smily
cutie_smily
 asked on

Break date range into a new row

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
Oracle DatabaseSQL

Avatar of undefined
Last Comment
cutie_smily

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Naveen Kumar

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Swadhin Ray

>> 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
Sean Stuber

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

can you explain what "aligned correctly" means?
SOLUTION
Metehan Ozcullu

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
cutie_smily

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Swadhin Ray

If you got your issue resolved you can close this question or else you can provide the answers for the experts asked you.
cutie_smily

ASKER
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.