Separating date ranges into separate segments in SQL
- by Richard
I have a table containing 2 date fields and an identifier (id, fromdate and todate) These dates overlap in any and every possible way. I need to produce a list of segments each with a start and end date describing the separate segments in that list.
For example:
id, FromDate ToDate
1, 1944-12-11, 1944-12-31
2, 1945-01-01, 1945-12-31
3, 1945-01-01, 1945-06-30
4, 1945-12-31, 1946-05-01
5, 1944-12-17, 1946-03-30
Should produce all the segments of all the overlaps:
1, 1944-12-11, 1944-12-16
1, 1944-12-17, 1944-12-31
5, 1944-12-17, 1944-12-31
2, 1945-01-01, 1945-06-30
3, 1945-01-01, 1945-06-30
5, 1945-01-01, 1945-06-30
2, 1945-07-01, 1945-12-09
5, 1945-07-01, 1945-12-09
2, 1945-12-10, 1945-12-31
4, 1945-12-10, 1945-12-31
5, 1945-12-10, 1945-12-31
4, 1946-01-01, 1946-03-30
5, 1946-01-01, 1946-03-30
4, 1946-04-01, 1946-05-01
Or perhaps a diagram might help
INPUT
1 <---->
2 <----------->
3 <----->
4 <---------->
5 <----------------->
OUTPUT
1 <->
1 <->
5 <->
2 <----->
3 <----->
5 <----->
2 <->
5 <->
2 <->
4 <->
5 <->
4 <->
5 <->
4 <---->
Please help
