none
Updating table rows with overlapping dates RRS feed

  • Question

  • Good day everyone. I am actually having a table as following:

    Table Name: PromotionList

    Note that the NULL in endDate means no end date or infinite end date.

    ID PromotionID StartDate   EndDate      Flag
    1  1           2015-04-05  2015-05-28   0
    2  1           2015-04-23  NULL         0
    3  2           2015-03-03  2015-05-04   0
    4  1           2015-04-23  2015-05-29   0
    5  1           2015-01-01  2015-02-02   0

    And I would like to produce the following outcome to the same table (using update statement):

    As what you all observe, it merge all overlapping dates based on same promotion ID by taking the minimum start date and maximum end date. Only the first row of overlapping date is updated to the desired value and the flag value change to 1. For other overlapping value, it will be set to NULL and the flag becomes 2.

    Flag = 1, success merge row. Flag = 2, fail row

    ID PromotionID StartDate   EndDate      Flag
    1  1           2015-04-05  NULL         1
    2  1           NULL        NULL         2
    3  2           2015-03-03  2015-05-04   1
    4  1           NULL        NULL         2
    5  1           2015-01-01  2015-02-02   1


    The second part that I would like to acheive is based on the first table as well. However, this time I would like to merge the date which results in the minimum start date and End Date of the last overlapping rows. Since the End date of the last overlapping rows of promotion ID 1 is row with ID 4 with End Date 2015-05-29, the table will result as follow after update.

    ID PromotionID StartDate   EndDate      Flag
    1  1           2015-04-05  2015-05-29   1
    2  1           NULL        NULL         2
    3  2           2015-03-03  2015-05-04   1
    4  1           NULL        NULL         2
    5  1           2015-01-01  2015-02-02   1

    Note that above is just sample Data. Actual data might contain thousands of records and hopefully it can be done in single update statement.

    Hope someone could help.


    • Edited by Inf1nity999 Tuesday, December 1, 2015 2:43 AM
    Tuesday, December 1, 2015 2:41 AM

Answers


  • Hi Inf1nity999,

    I thought the last output was what required. To get both output, please see below sample.
    DECLARE @T TABLE (ID INT IDENTITY ,PromotionID INT, StartDate DATE,   EndDate DATE, Flag INT)
    INSERT INTO @T(PromotionID,StartDate,EndDate,Flag) VALUES
    (1,'2015-04-05','2015-05-28',0),
    (1,'2015-04-23',NULL,0),
    (2,'2015-03-03','2015-05-04',0),
    (1,'2015-04-23','2015-05-29',0),
    (1,'2015-01-01','2015-02-02',0);
    
    ;WITH Cte AS
    (
    SELECT PromotionID,StartDate,COALESCE(EndDate,'99990101') EndDate,ROW_NUMBER() OVER(PARTITION BY PromotionID ORDER BY StartDate,COALESCE(EndDate,'99990101')) RN FROM @T T 
    WHERE FLAG=0
    ),Cte2 AS 
    (
    SELECT C.*,CASE WHEN (C2.StartDate IS NULL OR C2.EndDate>C.StartDate) THEN 1 ELSE 0 END IsCont FROM Cte C LEFT JOIN Cte C2 ON C.PromotionID=C2.PromotionID AND C.RN=C2.RN+1
    )
    ,Cte3 AS
    (
    SELECT PromotionID,MIN(StartDate) StartDate,MAX(EndDate) EndDate FROM CTE2 C CROSS APPLY(SELECT MAX(RN) grpBy FROM Cte2 WHERE IsCont = 0 AND RN<=C.RN AND PromotionID=c.PromotionID) cat
    GROUP BY PromotionID,grpBy
    ) 
    SELECT ID,T.PromotionID,C.StartDate, CASE C.EndDate WHEN '99990101' THEN NULL ELSE C.EndDate END EndDate,CASE WHEN C.StartDate IS NULL THEN 2 ELSE 1 END FLAG
    FROM @T T LEFT JOIN Cte3 C on c.PromotionID=t.PromotionID AND c.StartDate=t.StartDate
    
    
    ;WITH Cte AS
    (
    SELECT PromotionID,StartDate,COALESCE(EndDate,'99990101') EndDate,ROW_NUMBER() OVER(PARTITION BY PromotionID ORDER BY StartDate,COALESCE(EndDate,'99990101')) RN FROM @T T 
    WHERE FLAG=0
    ),Cte2 AS 
    (
    SELECT C.*,CASE WHEN (C2.StartDate IS NULL OR C2.EndDate>C.StartDate) THEN 1 ELSE 0 END IsCont FROM Cte C LEFT JOIN Cte C2 ON C.PromotionID=C2.PromotionID AND C.RN=C2.RN+1
    )
    ,Cte3 AS
    (
    SELECT PromotionID,MIN(StartDate) StartDate,MAX(CASE WHEN EndDate='99990101' THEN NULL ELSE EndDate END) EndDate FROM CTE2 C CROSS APPLY(SELECT MAX(RN) grpBy FROM Cte2 WHERE IsCont = 0 AND RN<=C.RN AND PromotionID=c.PromotionID) cat
    GROUP BY PromotionID,grpBy
    ) 
    SELECT ID,T.PromotionID,C.StartDate, C.EndDate EndDate,CASE WHEN C.StartDate IS NULL THEN 2 ELSE 1 END FLAG
    FROM @T T LEFT JOIN Cte3 C on c.PromotionID=t.PromotionID AND c.StartDate=t.StartDate

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    Tuesday, December 1, 2015 11:14 AM
    Moderator

All replies

  • Hi Inf1nity999,

    To get the expected output, please see below sample.

    DECLARE @T TABLE (ID INT IDENTITY ,PromotionID INT, StartDate DATE,   EndDate DATE, Flag INT)
    INSERT INTO @T(PromotionID,StartDate,EndDate,Flag) VALUES
    (1,'2015-04-05','2015-05-28',0),
    (1,'2015-04-23',NULL,0),
    (2,'2015-03-03','2015-05-04',0),
    (1,'2015-04-23','2015-05-29',0),
    (1,'2015-01-01','2015-02-02',0);
    
    ;WITH Cte AS
    (
    SELECT PromotionID,StartDate,COALESCE(EndDate,'99990101') EndDate,ROW_NUMBER() OVER(PARTITION BY PromotionID ORDER BY StartDate,COALESCE(EndDate,'99990101')) RN FROM @T T 
    WHERE FLAG=0
    ),Cte2 AS 
    (
    SELECT C.*,CASE WHEN (C2.StartDate IS NULL OR C2.EndDate>C.StartDate) THEN 1 ELSE 0 END IsCont FROM Cte C LEFT JOIN Cte C2 ON C.PromotionID=C2.PromotionID AND C.RN=C2.RN+1
    )
    ,Cte3 AS
    (
    SELECT PromotionID,MIN(StartDate) StartDate,MAX(CASE WHEN EndDate='99990101' THEN NULL ELSE EndDate END) EndDate FROM CTE2 C CROSS APPLY(SELECT MAX(RN) grpBy FROM Cte2 WHERE IsCont = 0 AND RN<=C.RN AND PromotionID=c.PromotionID) cat
    GROUP BY PromotionID,grpBy
    )
    UPDATE T
    SET StartDate=c.StartDate,
    	EndDate = c.EndDate,
    	Flag= CASE WHEN C.StartDate IS NULL THEN 2 ELSE 1 END
    FROM @T T LEFT JOIN Cte3 C on c.PromotionID=t.PromotionID AND c.StartDate=t.StartDate
    
    
    SELECT * FROM @T

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    Tuesday, December 1, 2015 8:59 AM
    Moderator
  • Eric, Thanks for the feedback. However, the code you provide does not take the largest end date (which is null end date ) if null end date exists. The ID 1 should be 2015-04-04 (StartDate) and NULL(EndDate).
    Tuesday, December 1, 2015 10:01 AM

  • Hi Inf1nity999,

    I thought the last output was what required. To get both output, please see below sample.
    DECLARE @T TABLE (ID INT IDENTITY ,PromotionID INT, StartDate DATE,   EndDate DATE, Flag INT)
    INSERT INTO @T(PromotionID,StartDate,EndDate,Flag) VALUES
    (1,'2015-04-05','2015-05-28',0),
    (1,'2015-04-23',NULL,0),
    (2,'2015-03-03','2015-05-04',0),
    (1,'2015-04-23','2015-05-29',0),
    (1,'2015-01-01','2015-02-02',0);
    
    ;WITH Cte AS
    (
    SELECT PromotionID,StartDate,COALESCE(EndDate,'99990101') EndDate,ROW_NUMBER() OVER(PARTITION BY PromotionID ORDER BY StartDate,COALESCE(EndDate,'99990101')) RN FROM @T T 
    WHERE FLAG=0
    ),Cte2 AS 
    (
    SELECT C.*,CASE WHEN (C2.StartDate IS NULL OR C2.EndDate>C.StartDate) THEN 1 ELSE 0 END IsCont FROM Cte C LEFT JOIN Cte C2 ON C.PromotionID=C2.PromotionID AND C.RN=C2.RN+1
    )
    ,Cte3 AS
    (
    SELECT PromotionID,MIN(StartDate) StartDate,MAX(EndDate) EndDate FROM CTE2 C CROSS APPLY(SELECT MAX(RN) grpBy FROM Cte2 WHERE IsCont = 0 AND RN<=C.RN AND PromotionID=c.PromotionID) cat
    GROUP BY PromotionID,grpBy
    ) 
    SELECT ID,T.PromotionID,C.StartDate, CASE C.EndDate WHEN '99990101' THEN NULL ELSE C.EndDate END EndDate,CASE WHEN C.StartDate IS NULL THEN 2 ELSE 1 END FLAG
    FROM @T T LEFT JOIN Cte3 C on c.PromotionID=t.PromotionID AND c.StartDate=t.StartDate
    
    
    ;WITH Cte AS
    (
    SELECT PromotionID,StartDate,COALESCE(EndDate,'99990101') EndDate,ROW_NUMBER() OVER(PARTITION BY PromotionID ORDER BY StartDate,COALESCE(EndDate,'99990101')) RN FROM @T T 
    WHERE FLAG=0
    ),Cte2 AS 
    (
    SELECT C.*,CASE WHEN (C2.StartDate IS NULL OR C2.EndDate>C.StartDate) THEN 1 ELSE 0 END IsCont FROM Cte C LEFT JOIN Cte C2 ON C.PromotionID=C2.PromotionID AND C.RN=C2.RN+1
    )
    ,Cte3 AS
    (
    SELECT PromotionID,MIN(StartDate) StartDate,MAX(CASE WHEN EndDate='99990101' THEN NULL ELSE EndDate END) EndDate FROM CTE2 C CROSS APPLY(SELECT MAX(RN) grpBy FROM Cte2 WHERE IsCont = 0 AND RN<=C.RN AND PromotionID=c.PromotionID) cat
    GROUP BY PromotionID,grpBy
    ) 
    SELECT ID,T.PromotionID,C.StartDate, C.EndDate EndDate,CASE WHEN C.StartDate IS NULL THEN 2 ELSE 1 END FLAG
    FROM @T T LEFT JOIN Cte3 C on c.PromotionID=t.PromotionID AND c.StartDate=t.StartDate

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    Tuesday, December 1, 2015 11:14 AM
    Moderator
  • Create TABLE test(ID INT IDENTITY ,PromotionID INT, StartDate DATE,   EndDate DATE, Flag INT)
    INSERT INTO test  VALUES
    (1,'2015-04-05','2015-05-28',0),
    (1,'2015-04-23',NULL,0),
    (2,'2015-03-03','2015-05-04',0),
    (1,'2015-04-23','2015-05-29',0),
    (1,'2015-01-01','2015-02-02',0);
    
    
    
    --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    Union all
    Select n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
      
    ,myDateCTE as
    (
    select dateadd(day,n-1,(select min(StartDate) from test)) dt ,PromotionID
    from Nums,(Select distinct  PromotionID from test) t
    Where dateadd(day,n-1,(select min(StartDate) from test))<=(select max(EndDate) from test)
    )
      
     
     ,mycte2 as (
    SELECT   PromotionID, dt, DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY PromotionID ORDER BY dt), dt) AS grp
    FROM myDateCTE t1
       WHERE  EXISTS 
         (SELECT PromotionID, StartDate,EndDate FROM test t2 
           WHERE t1.dt BETWEEN t2.StartDate AND t2.EndDate 
           and t1.PromotionID=t2.PromotionID) )
    
    	   ,mycte3 as (
      
    Select PromotionID,  Min(dt)  StartDate  , Max(dt)  EndDate
    From mycte2  
    Group By PromotionID,grp)
    
    Merge test t
    Using (
    Select t.ID, t.PromotionID, m.StartDate,m.EndDate
    , Case when m.PromotionID is null then 2 else 1 end as Flag
    FROM test t left join mycte3 m on m.PromotionID=t.PromotionID AND  m.StartDate=t.StartDate
    
    ) Src on t.ID=src.ID
    WHEN matched then
    Update Set 
    StartDate=src.StartDate,EndDate=src.EndDate, Flag=src.Flag;
    
    Select * from test
    
     
    drop table   Test
    
    /*
    ID	PromotionID	StartDate	EndDate	flag
    1	1	2015-04-05	2015-05-29	1
    2	1	NULL	NULL	2
    3	2	2015-03-03	2015-05-04	1
    4	1	NULL	NULL	2
    5	1	2015-01-01	2015-02-02	1
    
    */

    Tuesday, December 1, 2015 3:15 PM
    Moderator
  • For the first resultset, you can modify the query with a CASE expression:

    Create TABLE test(ID INT IDENTITY ,PromotionID INT, StartDate DATE,   EndDate DATE, Flag INT)
    INSERT INTO test  VALUES
    (1,'2015-04-05','2015-05-28',0),
    (1,'2015-04-23',NULL,0),
    (2,'2015-03-03','2015-05-04',0),
    (1,'2015-04-23','2015-05-29',0),
    (1,'2015-01-01','2015-02-02',0);
    
    
    
    --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    Union all
    Select n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
      
    ,myDateCTE as
    (
    select dateadd(day,n-1,(select min(StartDate) from test)) dt ,PromotionID
    from Nums,(Select distinct  PromotionID from test) t
    Where dateadd(day,n-1,(select min(StartDate) from test))<=(select max(EndDate) from test)
    )
      
     
     ,mycte2 as (
    SELECT   PromotionID, dt, DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY PromotionID ORDER BY dt), dt) AS grp
    FROM myDateCTE t1
       WHERE  EXISTS 
         (SELECT PromotionID, StartDate,EndDate FROM test t2 
           WHERE t1.dt BETWEEN t2.StartDate AND t2.EndDate 
           and t1.PromotionID=t2.PromotionID) )
    
    	   ,mycte3 as (
      
    Select PromotionID,  Min(dt)  StartDate  , Max(dt)  EndDate
    From mycte2  
    Group By PromotionID,grp)
    
       ,mycte4 as (
    	   Select t.ID, t.PromotionID, m.StartDate,m.EndDate
    , Case when m.PromotionID is null then 2 else 1 end as Flag
    FROM test t left join mycte3 m on m.PromotionID=t.PromotionID AND  m.StartDate=t.StartDate)
    
    
    ---Result1
    Merge test t
    Using mycte4 Src on t.ID=src.ID
    WHEN matched then
    Update Set 
    StartDate=src.StartDate,EndDate=Case when t.EndDate<src.EndDate then null else src.EndDate End, Flag=src.Flag;
    /*
    ID	PromotionID	StartDate	EndDate	Flag
    1	1	2015-04-05	NULL	1
    2	1	NULL	NULL	2
    3	2	2015-03-03	2015-05-04	1
    4	1	NULL	NULL	2
    5	1	2015-01-01	2015-02-02	1
    
    */

    Tuesday, December 1, 2015 8:57 PM
    Moderator
  • Hello, sorry for inconvenience. I am not able to login my previous account but I have extend the question in this page. Hope you can help. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/284ed5d2-9606-4d63-83dd-34cb6528ca95/updating-table-rows-with-overlapping-dates-extend
    • Edited by Inf1nity99 Wednesday, December 2, 2015 2:11 AM
    Wednesday, December 2, 2015 2:09 AM
  • Hello, sorry for inconvenience. I am not able to login my previous account but I have extend the question in this page. Hope you can help. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/284ed5d2-9606-4d63-83dd-34cb6528ca95/updating-table-rows-with-overlapping-dates-extend
    • Edited by Inf1nity99 Wednesday, December 2, 2015 2:12 AM
    Wednesday, December 2, 2015 2:10 AM
  • please close this thread before another related requirement in a new thread, you could mark the replies that help to achieve your requirement.
    Wednesday, December 2, 2015 2:18 AM