Finding Time overlaps for two differen timlines

Question

I am using SQL SERVER. I have two tables table1 and table2. Both of them store time intervals, for simplicity just say both has two datetime2 column, column names are S1 and S2 for table 1, T1 and T2 for table2, for each row S1 is Greater than S2 , exactly for the table two. I want to calculate the value of intervals between S2 and S1(like a timeline) and minus it from overlap of T1 and T2 over S1 and S2. I tried this but can't go further than first part of calculation

DECLARE @x float
SET x=0
SELECT SUM(S1-S2)-x from table1
 (set x =(SELECT (T1-T2) FROM table2
 WHERE T1>=S1 and T2<=S2));

Example:

S2= 10/25/2012 ; S1= 11/30/2012;


assume that we have three rows in table 2

T2=10/20/2012 , T1=10/28/2012

T2=11/4/2012  , T1=11/8/2012

T2=11/22/2012 , T1=11/30/2012

what I want is to find total minutes between S1 and S2 except the minutes that overlapped with second table T1 and T2 intervals. My query works for the second row in second table when the whole interval between T1 and T2 is in the interval of S1 and S2. This is somehow complicated hope this example helps

Query works fine but i can not calculate the overlap value with the query when one of the T1 or T2 are in the S1 and S2 interval. Should i run multiple queries? What are the parallels here?

Answer 1

I'm using SQL Server 2008 for this example. This solution assumes that all intervals in table T do not overlap with each other.

The following articles explain interval algebra in detail and I think they are a very good read. They have nice diagrams as well.

Comparing date ranges

http://salman-w.blogspot.com.au/2012/06/sql-query-overlapping-date-ranges.html

http://www.ics.uci.edu/~alspaugh/cls/shr/allen.html

http://stewashton.wordpress.com/2014/03/11/sql-for-date-ranges-gaps-and-overlaps/

Create tables with sample data

I named the columns in a less confusing manner than in the original question. I've added few extra intervals that do not overlap to illustrate that proposed solution filters them out.

DECLARE @TableS TABLE (ID int IDENTITY(1,1), DateFromS date, DateToS date);
DECLARE @TableT TABLE (ID int IDENTITY(1,1), DateFromT date, DateToT date);

INSERT INTO @TableS (DateFromS, DateToS) VALUES ('2012-10-25', '2012-11-30');
INSERT INTO @TableS (DateFromS, DateToS) VALUES ('2015-10-25', '2015-11-30');

INSERT INTO @TableT (DateFromT, DateToT) VALUES ('2012-10-20', '2012-10-28');
INSERT INTO @TableT (DateFromT, DateToT) VALUES ('2012-11-04', '2012-11-08');
INSERT INTO @TableT (DateFromT, DateToT) VALUES ('2012-11-22', '2012-11-30');
INSERT INTO @TableT (DateFromT, DateToT) VALUES ('2010-11-22', '2010-11-30');
INSERT INTO @TableT (DateFromT, DateToT) VALUES ('2020-11-22', '2020-11-30');

Find overlapping intervals

I assume that we want to do these calculations for each row in the table S and for each row in table T. If this is not the case, you should join tables with some extra condition.

In this example I work only with days precision, not minutes, and I assume that start and end dates are inclusive, i.e. duration between 01/01/2000 and 01/01/2000 is one day. It should be fairly straightforward to extend this to minute precision.

SELECT *
    ,ISNULL(1+DATEDIFF(day, MaxDateFrom.DateFrom, MinDateTo.DateTo), 0) AS OverlappedDays
FROM
    @TableS AS TS
    LEFT JOIN @TableT AS TT ON TS.DateFromS <= TT.DateToT AND TS.DateToS >= TT.DateFromT
    -- all periods in TS, which overlap with periods in TT
    --(StartA <= EndB)  and  (EndA >= StartB)
    CROSS APPLY
    (
        SELECT CASE WHEN TS.DateFromS > TT.DateFromT THEN TS.DateFromS ELSE TT.DateFromT END AS DateFrom
    ) AS MaxDateFrom
    CROSS APPLY
    (
        SELECT CASE WHEN TS.DateToS < TT.DateToT THEN TS.DateToS ELSE TT.DateToT END AS DateTo
    ) AS MinDateTo

The condition in LEFT JOIN leaves only overlapping intervals. To calculate the duration of the overlapping interval I use two CROSS APPLYs. This is the result set of this intermediary query:

ID   DateFromS    DateToS      ID   DateFromT    DateToT      DateFrom     DateTo       OverlappedDays
1    2012-10-25   2012-11-30   1    2012-10-20   2012-10-28   2012-10-25   2012-10-28   4
1    2012-10-25   2012-11-30   2    2012-11-04   2012-11-08   2012-11-04   2012-11-08   5
1    2012-10-25   2012-11-30   3    2012-11-22   2012-11-30   2012-11-22   2012-11-30   9
2    2015-10-25   2015-11-30   NULL NULL         NULL         NULL         NULL         0

Note, that the last row corresponds to the case when an interval in table S doesn't overlap with any intervals from table T.

Calculate durations

Now all we need is to sum up the duration of overlapping intervals T for each original row in table S and subtract it from the duration of the interval S.

SELECT
    TS.ID
    ,TS.DateFromS
    ,TS.DateToS
    ,1+DATEDIFF(day, TS.DateFromS, TS.DateToS) AS DurationS
    ,ISNULL(SUM(1+DATEDIFF(day, MaxDateFrom.DateFrom, MinDateTo.DateTo)), 0) AS DurationOverlapped
    ,1+DATEDIFF(day, TS.DateFromS, TS.DateToS)
    - ISNULL(SUM(1+DATEDIFF(day, MaxDateFrom.DateFrom, MinDateTo.DateTo)), 0) AS FinalDuration
FROM
    @TableS AS TS
    LEFT JOIN @TableT AS TT ON TS.DateFromS <= TT.DateToT AND TS.DateToS >= TT.DateFromT
    CROSS APPLY
    (
        SELECT CASE WHEN TS.DateFromS > TT.DateFromT THEN TS.DateFromS ELSE TT.DateFromT END AS DateFrom
    ) AS MaxDateFrom
    CROSS APPLY
    (
        SELECT CASE WHEN TS.DateToS < TT.DateToT THEN TS.DateToS ELSE TT.DateToT END AS DateTo
    ) AS MinDateTo
GROUP BY TS.ID, TS.DateFromS, TS.DateToS

This is the result set:

ID   DateFromS    DateToS      DurationS   DurationOverlapped   FinalDuration
1    2012-10-25   2012-11-30   37          18                   19
2    2015-10-25   2015-11-30   37          0                    37

You are interested in the FinalDuration value, which is 19 for your example and 37 for the second interval that I added for this example. You can add more intervals to the sample data to play with the queries and see how they work.

This solution assumes that all intervals in table T do not overlap with each other.