Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    5

    Unanswered: Overlaping Dates Sql 2005

    Can someone please assist me this? I 'm creating a spell episode date however the dates are overlapping.
    E.g
    Data
    Clientid Seqno Start date Enddate
    123 2 01/04/2007 to 05/05/2007
    123 2 10/10/2008 to 20/12/2008
    123 2 18/12/2008 to 01/02/2009
    123 2 01/03/2009 to
    123 2 04/04/2009 to 05/05/2009
    156 3 01/01/2011 to 03/03/2011
    160 3 01/01/2011


    How would I do the query which will show me result as

    123 01/04/2007 to 05/05/2007. Spell 1
    123 10/10/2008 to 01/02/2009 Spell 2
    123 01/03/2009 to 05/05/2009 Spell 3
    156 01/01/2011 to 03/03/2011 Spell 1
    160 01/01/2011 Spell 1

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I came up with a solution that is so ugly, there MUST be a better way to do it, but at least, it kinda works. Consider it as an assignment for the interested reader to come up with a beautiful solution (recursive SQL?).

    The way I tackle a problem like this is by drawing different periods (line segments) on a piece of paper, and decide how the system should react on the different scenario's:
    1) remove double periods (same Clientid, same StartDate and same EndDate), keep only 1 record per clientId/period.
    2) Delete all periods that fall within another (larger) period
    3) When two periods overlap: SD1 -- ED1 and SD2 -- ED2, Update period 1 to this situation: SD1 -- ED2 (the period 2 record will be deleted in step 2 in the next iteration through the loop).
    With each iteration through the loop, records are UPDATEd and/or DELETEd, hereby cleaning up the dataset. Repeat loop until no actions can be performed any more.

    Warning! Not for the faint of heart. Ugly code ahead.
    Code:
    DROP TABLE #DaTable
    CREATE TABLE #DaTable(
    	Id			int	identity (1, 1)	NOT NULL,
    	Clientid	int		NOT NULL,
    	StartDate	DATE	NOT NULL,
    	EndDate		DATE,
    	CONSTRAINT PK_DaTable PRIMARY KEY (Id)
    )
    
    INSERT INTO #DaTable(Clientid, StartDate, EndDate) VALUES
    (123, CONVERT(DATE, '01/04/2007', 103), CONVERT(DATE, '05/05/2007', 103)), 
    (123, CONVERT(DATE, '02/04/2007', 103), CONVERT(DATE, '05/05/2007', 103)),
    (123, CONVERT(DATE, '10/10/2008', 103), CONVERT(DATE, '20/12/2008', 103)), 
    (123, CONVERT(DATE, '18/12/2008', 103), CONVERT(DATE, '01/02/2009', 103)), 
    (123, CONVERT(DATE, '01/03/2009', 103), NULL),
    (123, CONVERT(DATE, '04/04/2009', 103), CONVERT(DATE, '05/05/2009', 103)), 
    (156, CONVERT(DATE, '01/01/2011', 103), CONVERT(DATE, '03/03/2011', 103)), 
    (156, CONVERT(DATE, '01/01/2011', 103), CONVERT(DATE, '03/03/2011', 103)), 
    (156, CONVERT(DATE, '01/01/2011', 103), CONVERT(DATE, '03/03/2011', 103)), 
    (160, CONVERT(DATE, '01/01/2011', 103), NULL)
    
    SELECT * FROM #DaTable
    GO
    
    DECLARE @ROWCOUNT INT
    SET @ROWCOUNT = 1
    
    WHILE @ROWCOUNT > 0
    BEGIN
    	-- remove double periods
    	WITH CTE AS 
    	(SELECT Id, 
    			Clientid, 
    			StartDate, 
    			Enddate,
    			ROW_NUMBER() OVER (Partition by Clientid, StartDate, Enddate ORDER BY Id) as RowNum
    	FROM #DaTable)
    	DELETE D
    	FROM #DaTable as D
    		INNER JOIN CTE ON
    			D.Clientid = CTE.Clientid AND
    			D.StartDate = CTE.StartDate AND
    			COALESCE(D.EndDate, '9999-12-31') = COALESCE(CTE.EndDate, '9999-12-31') AND
    			D.Id = CTE.Id AND
    			RowNum > 1
    			
    	SET @ROWCOUNT = @@ROWCOUNT
    	
    	-- Remove periods that are totally included in other periods
    	DELETE D
    	--SELECT D.*, T.*
    	FROM #DaTable as D
    		INNER JOIN #DaTable as T ON
    			D.Clientid = T.Clientid AND
    			D.Id <> T.Id AND
    			D.StartDate BETWEEN T.StartDate AND COALESCE(T.EndDate, '9999-12-31') AND
    			D.EndDate BETWEEN T.StartDate AND COALESCE(T.EndDate, '9999-12-31')
    
    	SET @ROWCOUNT = @ROWCOUNT + @@ROWCOUNT
    
    	-- Adjust EndDate of overlapping periods to last EndDate
    	UPDATE U
    	SET U.EndDate = T.EndDate
    	-- SELECT U.*, T.*
    	FROM #DaTable as U 
    		INNER JOIN #DaTable as T ON
    			U.Clientid = T.Clientid AND
    			U.Id <> T.Id AND
    			T.StartDate BETWEEN U.StartDate AND COALESCE(U.EndDate, '9999-12-31') AND
    			COALESCE(T.EndDate, '9999-12-31') >= COALESCE(U.EndDate, '9999-12-31')
    
    	SET @ROWCOUNT = @ROWCOUNT + @@ROWCOUNT
    END
    GO 
    
    WITH CTE AS 
    (SELECT Clientid, 
    		StartDate, 
    		Enddate,
    		ROW_NUMBER() OVER (Partition by Clientid ORDER BY StartDate) as RowNum
    FROM #DaTable)
    SELECT CTE.Clientid, CONVERT(VARCHAR(11), CTE.StartDate, 103) + 
    	COALESCE(' to ' + CONVERT(VARCHAR(11), CTE.EndDate, 103), '')  as period,
    	'Spell ' + CAST(RowNum AS VARCHAR(5)) as Spell
    FROM CTE
    		
    -- Result
    -- 123	01/04/2007 to 05/05/2007	Spell 1
    -- 123	10/10/2008 to 01/02/2009	Spell 2
    -- 123	01/03/2009			Spell 3
    -- 156	01/01/2011 to 03/03/2011	Spell 1
    -- 160	01/01/2011			Spell 1
    My result differs from yours, due to the way that I treat a NULL value in EndDate (End Of Time, Armageddon, 31/12/9999, ... whatever comes first). It was not clear to me what the business logic should be in that case.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2011
    Posts
    5

    Thumbs up Excellent

    Excellent.

    Many thanks

    Hus

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •