intersect time intervals and generating new ones depending on intersect-results

Question

hi there,

i've got two tables with time intervals and some settings for each interval, eg

2009-01-01T06:00:00  2009-01-04T14:00:00  1  0
2009-01-04T22:00:00  2009-01-07T06:00:00  2  1
2009-01-07T06:00:00  2009-01-09T13:00:00  2  2
2009-01-09T22:00:00  2009-01-14T06:00:00  3  0

and

2009-01-04T16:00:00  2009-01-05T01:00:00  2  0
2009-01-05T06:00:00  2009-01-06T14:00:00  1  1
2009-01-07T08:00:00  2009-01-10T16:00:00  1  0

result:

2009-01-01T06:00:00  2009-01-04T14:00:00  1  0
2009-01-04T16:00:00  2009-01-06T14:00:00  1  1
2009-01-06T22:00:00  2009-01-07T06:00:00  2  1
2009-01-07T08:00:00  2009-01-10T16:00:00  1  0
2009-01-10T22:00:00  2009-01-14T06:00:00  3  0

the query i aim for should - check each interval of table 2 with each interval of table 1 - include interval from table 1 in the result, if no interval of table 2 intersects or overlaps - generate new intervals if intersection or overlapping is detected, regarding that if dataset from t1 intersected by t2 could be cutted and a smaller interval (not intersected by t2) should keep alive:

t1: 2009-01-01T06:00:00  2009-01-10T14:00:00
t2: 2009-01-03T08:00:00  2009-01-09T16:00:00

sould result in

2009-01-01T06:00:00  2009-01-02T14:00:00
2009-01-03T08:00:00  2009-01-09T16:00:00
2009-01-10T06:00:00  2009-01-10T14:00:00

intervals in t1 and t2 are continous intervals using one setting, each. they can be splitted in several intervals per original one, eg

2009-01-01T06:00:00  2009-01-03T14:00:00 1 0

is the "compressed" result of the three intervals:

2009-01-01T06:00:00  2009-01-03T14:00:00 1 0
2009-01-02T06:00:00  2009-01-02T14:00:00 1 0
2009-01-03T06:00:00  2009-01-03T14:00:00 1 0

compression is allowed, if time-parts are identical, as the setting-values should be.

thanks in advance for any hints!

edit: platform is sql server 2008.

Answer 1

Hello Gavin,

thanks for your reply - I have to apologize. You're indeed absolutely right to put the second row in question. Sorry to keep you staring at this false result :( Since I've done nothing else than juggle with times, my head seemed to be dizzy or something.

The result should be something like this, I hope ;)

R1 2009-01-01T06:00:00  2009-01-04T14:00:00  1  0
R2 2009-01-04T16:00:00  2009-01-05T01:00:00  2  0
R3 2009-01-05T06:00:00  2009-01-06T14:00:00  1  1
R4 2009-01-05T22:00:00  2009-01-07T06:00:00  2  1 
R5 2009-01-07T08:00:00  2009-01-10T16:00:00  1  0
R6 2009-01-09T22:00:00  2009-01-14T06:00:00  3  0

R1: 1A
R2: 2A, removed last part ot interval, since 1B would intersect.
R3: 2B
R4: 1B, removed first day of interval, since R2 would intersect.
R5: 2C ( 1C is removed completely, since 2C intersects )
R6: 1D

The "settings" fields are relevant to shrink the number of rows in a further step. All rows can be compressed to one, if settings and the time-parts of the datetime are equal.