rock rock - 6 months ago 14
SQL Question

How to find the time overlap in MSSQL

I want to get the count of start time overlap for DocID

In the below example 9:00-9:15 overlaps in 9:00-9:30
So i want the count as 2 for DocID (111)

=============================================================
EncID | Pid | DocID | Date | StartTime | EndTime|
=============================================================
1 | 11 | 111 | 25/3/2016 | 9:00 | 9:30 |
2 | 12 | 111 | 25/3/2016 | 9:00 | 9:15 |
3 | 13 | 111 | 26/3/2016 | 10:00 | 10:30 |
=============================================================





Expected Out put
==============
DocID | count|
==============
111 | 2 |
==============

Answer

Something like this should work and avoids OR. The subquery should run efficiently.

SELECT 
    T1.DocID,
    (
        SELECT COUNT(*)
        FROM [Table] T2
        WHERE T1.DocID = T2.DocID
            AND T1.[Date] = T2.[Date]
            AND T1.StartTime <= T2.EndTime
            AND T1.EndTime >= T2.StartTime
    ) AS [Count]
FROM [Table] T1
GROUP BY
    T1.DocID

If you're also wanting to filter to only show records with overlap, it's probably best to convert the sub-query to a join (not any more efficient, but definitely easier to filter):

SELECT 
    T1.DocID,
    COUNT(*) AS [Count]
FROM [Table] T1
    INNER JOIN [Table] T2
        ON T1.DocID = T2.DocID
            AND T1.[Date] = T2.[Date]
            AND T1.StartTime <= T2.EndTime
            AND T1.EndTime >= T1.EndTime
GROUP BY
    T1.DocID
HAVING
    COUNT(*) > 1
Comments