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:
Advertisement
Answer
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