Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

t-SQL to update table to remove overlapping time frames

I was wondering if someone could help me with this SQL statement?

Say, I have an SQL Server 2008 table like this:

id -- INT PRIMARY KEY
dtIn -- DATETIME2
dtOut -- DATETIME2
type -- INT

id  dtIn    dtOut    type
1   05:00   10:00    1
2   08:00   16:00    2
3   02:00   08:00    1
4   07:30   11:00    1
5   07:00   12:00    2

I need to remove any time overlaps in the table above. This can be illustrated with this diagram: enter image description here

So I came up with this SQL:

UPDATE [table] AS t 
SET dtOut = (SELECT MIN(dtIn) FROM [table] WHERE type = t.type AND t.dtIn >= dtIn AND t.dtIn < dtOut) 
WHERE type = t.type AND t.dtIn >= dtIn AND t.dtIn < dtOut

But it doesn't work. Any idea what am I doing wrong here?

****EDIT****

OK, it took me awhile to get to this. Seems to be a working SQL for what I need it for:

--BEGIN TRANSACTION;

--delete identical dtIn
DELETE dT1
FROM tbl dT1
WHERE EXISTS
(
    SELECT *
    FROM tbl dT2
    WHERE dT1.Type = dT2.Type
    AND dT1.dtIn = dT2.dtIn
    AND (
            dT1.dtOut < dT2.dtOut
            OR (dT1.dtOut = dT2.dtOut AND dT1.id < dT2.id)
        )
);

--adjust dtOuts to the max dates for overlapping section
UPDATE tbl
SET dtOut = COALESCE((
    SELECT MAX(dtOut)
    FROM tbl as t1
    WHERE t1.type = tbl.type
    AND t1.dtIn < tbl.dtOut 
AND t1.dtOut > tbl.dtIn
    ), dtOut);

-- Do the actual updates of dtOut
UPDATE tbl
SET dtOut = COALESCE((
    SELECT MIN(dtIn)
    FROM tbl as t2
    WHERE t2.type = tbl.type AND
          t2.id <> tbl.id AND
          t2.dtIn >= tbl.dtIn AND t2.dtIn < tbl.dtOut
    ), dtOut);

--COMMIT TRANSACTION;
like image 650
ahmd0 Avatar asked Jul 19 '12 22:07

ahmd0


People also ask

How do you get rid of OverLapping dates in SQL?

let's pick the big date SELECT ID, EMP_ID, [START DATE], MAX(END DATE) FROM (SELECT ID, EMP_ID, TEAM, [END DATE], MIN([START DATE]) [START DATE] FROM my_table GROUP BY ID, EMP_ID, END_DATE ) a GROUP BY ID, EMP_ID, [START DATE] -- Now we are done with similar end date and similar start date -- At this point I will write ...

How to check OverLapping time in SQL?

SQL Query using Lag Function for OverLapping Time Intervals SQL programmers now can use SQL LAG() function to compare all table rows with the previous row when ordered by ID column. Maybe it would be better to select the compared previous row using LAG() function sorting rows by StarDate.


1 Answers

I think CROSS APPLY might do the trick:

DECLARE @T TABLE (ID INT, DTIn DATETIME2, dtOut DATETIME2, Type INT)
INSERT @T VALUES
(1, '05:00', '10:00', 1),
(2, '08:00', '16:00', 2),
(3, '02:00', '08:00', 1),
(4, '07:30', '11:00', 1),
(5, '07:00', '12:00', 2)

UPDATE  @T
SET     DtOut = T3.DtOut
FROM    @T T1
        CROSS APPLY
        (   SELECT  MIN(DtIn) [DtOut]
            FROM    @T T2
            WHERE   T2.Type = T1.Type
            AND     T2.DtIn > T1.dtIn 
            AND     T2.DtIn < T1.dtOut
        ) T3
WHERE   T3.dtOut IS NOT NULL

SELECT  *
FROM    @T
like image 153
GarethD Avatar answered Nov 14 '22 23:11

GarethD