Calculate time correctly between 2 date range

user_6260157 0
Viewed 72

I am working with the Datetime column where I am having trouble calculating the Time because of overlapping in time.

This is my sample data

    Declare @T table (ID Int, InTime Datetime,OutTime Datetime)
    Insert into @T values (1,'2020-08-23 09:26:07.000','2020-08-23 09:57:55.000')
    Insert into @T values (1,'2020-08-23 14:09:08.000','2020-08-26 08:13:45.000')
    Insert into @T values (1,'2020-08-24 11:14:37.000','2020-08-24 18:07:25.000')
    Insert into @T values (1,'2020-08-25 09:19:38.000','2020-08-25 19:19:29.000')
    Insert into @T values (1,'2020-08-26 08:13:50.000','2020-08-28 08:39:23.000')
    Insert into @T values (1,'2020-08-27 08:42:16.000','2020-08-27 11:38:06.000')
    Insert into @T values (1,'2020-08-27 09:51:14.000','2020-08-28 18:23:06.000')
    Insert into @T values (1,'2020-08-29 09:51:14.000','2020-08-30 18:23:06.000')

My Expected Output:

+----+-------------------------+--------------------------+
| ID |         InTime          |         OutTime          |
+----+-------------------------+--------------------------+
|  1 | 2020-08-23 09:26:07.000 | 2020-08-23 09:57:55.000  |
|  1 | 2020-08-23 14:09:08.000 | 2020-08-26 08:13:45.000  |
|  1 | 2020-08-26 08:13:50.000 | 2020-08-28 08:39:23.000  |
|  1 | 2020-08-29 09:51:14.000 | 2020-08-30 18:23:06.000  |
+----+-------------------------+--------------------------+

If you see the 2nd entry in a table where my Intime is 2020-08-23 14:09:08.000 and outTime is 2020-08-26 08:13:45.000. So if there is any entry in a table between 23 and 26 we should skip that entry. so In out table we need to skip 24th and 25th entry.

Can someone please help me with this query. Any help will be appreciated

I tried this link but couldn't understand the logic Link

gaps-and-islands datetime sql-server-2008 sql-server sql
1 Answers

This is a classical case of "aggregating intervals". Snodgrass give a classical query to do that :

WITH  
T0 AS  
(SELECT PRE.id,
        PRE.intime AS D1, PRE.outtime AS F1,  
        DER.intime AS D2, DER.outtime AS F2  
 FROM   @T PRE  
        INNER JOIN @T DER  
              ON PRE.intime <= DER.outtime
                 AND PRE.id = DER.id)  
SELECT DISTINCT id, D1 AS intime, F2 AS outtime
FROM   T0 AS I  
WHERE  NOT EXISTS (SELECT *  
                   FROM   @T SI1  
                   WHERE  (SI1.intime < I.D1  
                           AND I.D1 <= SI1.outtime
                           AND I.id = SI1.id )  
                      OR  (SI1.intime <= I.F2  
                           AND I.F2 < SI1.outtime
                           AND I.id = SI1.id))  
AND NOT EXISTS (SELECT *  
                FROM   @T SI2
                WHERE  D1 < SI2.intime  
                  AND  SI2.intime <= F2
                  AND  I.id = SI2.id  
                  AND  NOT EXISTS (SELECT *  
                                   FROM   @T SI3  
                                   WHERE  SI3.intime < SI2.intime  
                                     AND  SI2.intime <= SI3.outtime
                                     AND  SI2.id = SI3.id ));

Chris Date give another version :

WITH T  
AS (SELECT F.intime, L.outtime, F.id
    FROM   @T AS F  
           JOIN @T AS L  
                ON F.outtime <= L.outtime
                   AND F.id = L.id
           INNER JOIN @T AS E      
                 ON F.id = E.id  
    GROUP  BY F.intime, L.outtime,  F.id  
    HAVING COUNT(CASE  
                    WHEN (E.intime < F.intime AND F.intime <= E.outtime)  
                          OR (E.intime <= L.outtime AND L.outtime < E.outtime)
                    THEN 1  
                 END) = 0)  
SELECT id, intime, MIN(outtime) AS outtime  
FROM   T  
GROUP  BY id, intime;

And finally one I wrote :

WITH  
T0 AS -- suprime les périodes incluses
(SELECT DISTINCT Tout.id, Tout.intime, Tout.outtime
 FROM   @T  AS Tout  
 WHERE  NOT EXISTS(SELECT *  
                   FROM   @T  AS Tin  
                   WHERE  Tout.intime >= Tin.intime  
                     AND  Tout.outtime < Tin.outtime
                     AND Tout.id = Tin.id)),  
T1 AS -- ancres : périodes de tête...  
(SELECT Ta.*, 1 AS ITERATION  
 FROM   T0 AS Ta  
 WHERE  NOT EXISTS(SELECT *  
                   FROM   T0 AS Tb  
                   WHERE  Tb.outtime >= Ta.intime  
                      AND Tb.outtime  < Ta.outtime
                      AND Tb.id = Ta.id)  
 UNION  ALL -- itération sur période dont le debut est inclus dans les bornes de la période ancre  
 SELECT T1.id, T1.intime, T0.outtime, T1.ITERATION + 1
 FROM   T1  
        INNER JOIN T0  
              ON T1.intime < T0.intime  
                 AND T1.outtime >= T0.intime  
                 AND T1.outtime < T0.outtime
                 AND T1.id = T0.id),  
T2 AS  
(SELECT *, ROW_NUMBER() OVER(PARTITION BY id, intime ORDER BY DATEDIFF(s, intime, outtime) DESC) AS N1,
           ROW_NUMBER() OVER(PARTITION BY id, outtime ORDER BY DATEDIFF(s, intime, outtime) DESC) AS N2
 FROM   T1)  
SELECT id, intime, outtime
FROM   T2  
WHERE  N1 = 1 AND N2 = 1;

Which is a recursive Query

Itzik Ben Gan has done some more sophisticated and performant queries...

user_126598720
Related