Calculate the time period a certain field is overlapping in SQL Server-CodePudding

I am currently trying to figure out the overlapping time period between records. I already have a query that indicates if it is overlapping another field with 'yes' and 'no'. But I need to have the exact duration of the overlapping time period when the field has 'yes' as indication. Can someone help me with it?

Query:

CASE 
    WHEN 
        ([StartDateTime] BETWEEN (LAG([StartDateTime], 1) OVER (ORDER BY [aaID])) 
                             AND (LAG([EndDateTime], 1) OVER (ORDER BY [aaID]))) OR
        ([EndDateTime] BETWEEN (LAG([StartDateTime], 1) OVER (ORDER BY [aaID])) 
                           AND (LAG([EndDateTime], 1) OVER (ORDER BY [aaID]))) OR
        ([StartDateTime] < (LAG([StartDateTime], 1) OVER (ORDER BY [aaID])) 
         AND [EndDateTime] > (LAG([EndDateTime], 1) OVER (ORDER BY [aaID]))) OR
        ([StartDateTime] > (LAG([StartDateTime], 1) OVER (ORDER BY [aaID])) 
         AND [EndDateTime] < (LAG([EndDateTime], 1) OVER (ORDER BY [aaID])))
        THEN 'yes'
    WHEN (LAG([StartDateTime], 1) OVER (ORDER BY [aaID])) IS NULL
        THEN NULL
    ELSE 'no'
END AS [OverLapping with Previous]

Result:

CodePudding user response：

Check this for the difference in days.

case when ([StartDateTime] >= (lag([StartDateTime],1) over (order by [aaIDID])) and [EndDateTime] <= (lag([EndDateTime],1) over (order by [aaIDID]))) 
     Then DateDiff(day, [StartDateTime], [EndDateTime])
     when ([StartDateTime] <= (lag([StartDateTime],1) over (order by [aaIDID])) and [EndDateTime] >= (lag([EndDateTime],1) over (order by [aaIDID])))
     Then DateDiff(day, (lag([StartDateTime],1) over (order by [aaIDID])), (lag([EndDateTime],1) over (order by [aaIDID])))
     when ([StartDateTime] < (lag([StartDateTime],1) over (order by [aaIDID])) and [EndDateTime] < (lag([EndDateTime],1) over (order by [aaIDID]))) And [EndDateTime] > (lag([StartDateTime],1) over (order by [aaIDID]))
     Then DateDiff(day, (lag([StartDateTime],1) over (order by [aaIDID])), [EndDateTime])
     when ([StartDateTime] > (lag([StartDateTime],1) over (order by [aaIDID])) and [EndDateTime] > (lag([EndDateTime],1) over (order by [aaIDID]))) And [StartDateTime] < (lag([EndDateTime],1) over (order by [aaIDID]))
     Then DateDiff(day, [StartDateTime], (lag([EndDateTime],1) over (order by [aaIDID])))
end as Diff

CodePudding user response：

A brute force approach, to find all overlapping pairs of ranges in a table, is to self join the table with itself where a.id < b.id and the range overlaps. The id check is will suppress duplicate pairs and same row pairs. The overlap is checked using the logic described here.

Once you have found out the overlapping pairs, calculating the overlapping portion is trivial.

SELECT
    a.id,
    b.id,
    CASE WHEN a.StartDateTime > b.StartDateTime THEN a.StartDateTime ELSE b.StartDateTime END AS overlap_start,
    CASE WHEN a.EndDateTime   < b.EndDateTime   THEN a.EndDateTime   ELSE b.EndDateTime   END AS overlap_end
FROM yourtable AS a
JOIN yourtable AS b ON
    a.id < b.id AND
    b.EndTime > a.StartTime AND a.EndTime > b.StartTime