CODEWITHSUNDEEP
sqlsql-servert-sql
rock
rock

Reputation: 45

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   |
==============

Upvotes: 2

Views: 693

Answers (1)

Jason W
Jason W

Reputation: 13179

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

Upvotes: 1

Related Questions