interval overlapping in tsql

Question

hi folks,

i need to get splited intervals and the number of overlapping intervals, eg

basedata:

interval A: startTime 08:00, endTime 12:00
interval B: startTime 09:00, endTime 12:00
interval C: startTime 12:00, endTime 16:00
interval D: startTime 13:00, endTime 14:00

now i have a separate interval from 10:00 to 15:00 and have to determine what intervals are intersected at first. result should be something like:

1: 10:00 - 12:00 ( intersecting with interval A )
2: 10:00 - 12:00 ( intersecting with interval B )
3: 12:00 - 15:00 ( intersecting with interval C )
4: 13:00 - 14:00 ( intersecting with interval D )

this part works fine, the following causes the trouble:

i need some kind of weighting for parallel intervals. this also means, that it can occur that an interval-intersection must be splitted n times, if it's ( partly ) intersected by another one.

in the upper example the expecting result would be:

1: 10:00 - 12:00 -> weightage: 50%
2: 10:00 - 12:00 -> weightage: 50%

3.1: 12:00 - 13:00 -> weightage: 1oo%
3.2: 13:00 - 14:00 -> weightage: 50%
3.3: 14:00 - 15:00 -> weightage: 50%

4: 13:00 - 14:00 -< weightage: 100%

the splitting of interval 3 is caused by the intersecting with interval 4 between 13:00 and 14:00.

sql-server is ms-sql 2008.

thanks for help in advance!

Answer 1

If I understand what you're trying to do correctly, shouldn't your expected result be

1: 10:00 - 12:00 -> weightage: 50%
2: 10:00 - 12:00 -> weightage: 50%

3.1: 12:00 - 13:00 -> weightage: 1oo%
3.2: 13:00 - 14:00 -> weightage: 50%
3.3: 14:00 - 15:00 -> weightage: 50%

4: 13:00 - 14:00 -< weightage: 50%

since 13:00-14:00 is used twice?