none
Previous Types for Closed Scenarios

    질문

  • This should be easy but I am running into all types of trouble trying to figure this one out. Here I have the following fields Store ID, Person_id, type_id, Type, created_at, start_date.

    Store ID             Person_id            type_id   Type              created_at                   start_date

    13458     11146402            1       Reported       12/18/07 2:39 PM      12/18/2007

    13458      11146402           6      Accepted       3/14/08 7:15 PM         3/14/2008

    13458      11146402           7      Closed           4/3/08 5:18 PM           4/3/2008

    13458      11146402           1      Reported        4/3/08 6:36 PM          4/3/2008

    13458      11146402           3      Review          4/3/08 6:36 PM          4/3/2008

    13458      11146402            7     Closed           12/9/09 5:00 AM       12/9/2009

    13458      11146402            1     Reported        2/3/10 8:54 PM         2/3/2010

    13458      11146402            3     Review          2/3/10 8:54 PM         2/3/2010

    13458      11146402            9     PreClosed      11/18/10 8:54 PM     11/18/2010

    13458      11146402            7     Closed           11/18/10 8:54 PM     11/18/2010

    13458      11146402            3     Review          6/16/16 4:34 PM       4/7/2011

    13458      11146402            7     Closed           6/16/16 4:34 PM       6/13/2016

    13458      11146402            1     Reported        2/3/10 8:54 PM         2/3/2010

    13458      11146402            3     Review          11/18/17 8:54 PM     2/3/2017

    13458      11146402            10   Completed     11/18/17 8:54 PM     11/18/2017

    13458      11146402            7     Closed           11/18/17 8:54 PM     02/18/2018

    13458      11146402            1     Reported        2/3/10 8:54 AM        2/3/2010

    13458      11146402            3     Review           2/3/10 1:54 PM        2/3/2010

    13458      11146402            7    Closed             2/3/10 4:54 PM         2/3/2010

    Note:

    Same store I’d and same person I’d has different scenarios. Here I need all the records which source type = 'Closed' but their previous type_id should be either of (3,9,10). Just an FYI…… type_id = 1 is start of respective episode and type = 7 is end of respective episode and there may be cases where it may not start with type_id = 1 but it may directly with (3, 9, 10) and then close with type_id = 7. And also we need to delete if it starts type_id = 1 and ended with type_id = 7 is within 24hrs.

    Here is the query I tried:

    ;WITH closed as (

    SELECT *,ROW_NUMBER() OVER(PARTITION BY store_id, person_id ORDER BY created_at) N

    FROM #Temp_P1_SK

    WHERE type_id = 7

    )

    SELECT c.store_id,c.person_id,c.type_id,c.created_at,c.startdate,P.type_id AS P_type_id

    FROM closed c

    LEFT JOIN closed p on c.store_id=p.store_id and c.person_id=p.person_id and c.N=p.N + 1

    Note: Here we can’t take the minimum of type id because they are not assigned in orderly manner. Even we can’t take minimum of created date too because they can create all one time and backdate the start date to their respective occurrence. The final output from this example should be four records with source type = 'closed' with start date of their respective episode start date

    Final output:

    Store ID        Person_id           type_id   Type      Pre_type_id     Pre_Type     start_date        Pre_start_date

    13458   11146402    7         Closed            3               Review          12/9/2009       4/3/2008

    13458   11146402   7           Closed           9               PreClosed      11/18/2010    11/18/2010

    13458   11146402   7           Closed           3              Review           6/13/2016       4/7/2011

    13458   11146402   7           Closed           10            Completed     02/18/2018      11/18/2017    


    Red



    • 편집됨 Red987 2018년 6월 14일 목요일 오후 2:36
    2018년 6월 14일 목요일 오후 2:19

답변

  • Here's the full illustration without using any 2012 only features

    CREATE TABLE #Episode (StoreID bigint, PersonId bigint, TypeId int, TypeDesc varchar(20), CreatedAt datetime, StartDate datetime)
    INSERT INTO #Episode
    VALUES 
    (13458,11146402,1,'Reported','12/18/2007  2:39:00 PM','12/18/2007'),
    (13458,11146402,6,'Accepted','12/19/2007 14:39','3/14/2008'),
    (13458,11146402,7,'Closed','12/20/2007 14:39','4/3/2008'),
    (13458,11146402,1,'Reported','12/21/2007 14:39','4/3/2008'),
    (13458,11146402,3,'Review','12/22/2007 14:39','4/3/2008'),
    (13458,11146402,7,'Closed','12/23/2007 14:39','12/9/2009'),
    (13458,11146402,1,'Reported','12/24/2007 14:39','2/3/2010'),
    (13458,11146402,3,'Review','12/25/2007 14:39','2/3/2010'),
    (13458,11146402,9,'PreClosed','12/26/2007 14:39','11/18/2010'),
    (13458,11146402,7,'Closed','12/27/2007 14:39','11/18/2010'),
    (13458,11146402,3,'Review','12/28/2007 14:39','4/7/2011'),
    (13458,11146402,7,'Closed','12/29/2007 14:39','6/13/2016'),
    (13458,11146402,1,'Reported','12/30/2007 14:39','2/3/2010'),
    (13458,11146402,3,'Review','12/31/2007 14:39','2/3/2017'),
    (13458,11146402,10,'Completed','1/1/2008 14:39','11/18/2017'),
    (13458,11146402,7,'Closed','1/2/2008 14:39','2/18/2018'),
    (13458,11146402,1,'Reported','1/3/2008 14:39','2/3/2010'),
    (13458,11146402,3,'Review','1/4/2008 14:39','2/3/2010'),
    (13458,11146402,7,'Closed','1/5/2008 14:39','2/3/2010')
    
    ;With CTE
    AS
    (
    select *,ROW_NUMBER() OVER (PARTITION BY StoreID,PersonId,TypeId ORDER BY CreatedAt) AS Seq
    from #Episode e
    WHERE TypeId = 7
    ),
    CTE2
    AS
    (
    SELECT  c1.*,c3.*
    FROM CTE c1
    LEFT JOIN CTE c2
    ON c2.Seq = c1.Seq -1
    AND c2.PersonId = c1.PersonId
    AND c2.StoreID = c1.StoreID
    CROSS APPLY
    (
    SELECT MIN(StartDate) AS GrpStart,
    MIN(CASE WHEN typeid = 1THEN StartDate END) AS Type1Date,
    MAX(CreatedAt) AS GrpEnd
    FROM #Episode
    WHERE StoreID = c1.StoreID
    AND PersonId = c1.PersonId
    AND CreatedAt < c1.CreatedAt
    AND (CreatedAt > c2.CreatedAt OR c2.CreatedAt IS NULL)
    )c3
    WHERE DATEADD(HH,24,GrpStart) < c1.StartDate
    OR Type1Date IS NULL
    )
    
    SELECT c.StoreID,c.PersonId,c.TypeId,c.TypeDesc,e.TypeId AS PreTypeID,e.TypeDesc AS PreType,c.StartDate,e.StartDate AS PreSTartDate
    FROM CTE2 c
    JOIN #Episode e
    ON e.StoreID = c.StoreID
    AND e.PersonID = e.PersonID
    AND e.CreatedAt = c.GrpEnd
    WHERE e.TypeId IN (3,9,10)
    ORDER BY StoreID,PersonId,StartDate
    
    
    
    
    /*
    Output
    -----------------------------------------------------------
    StoreID	PersonId	TypeId	TypeDesc	PreTypeID	PreType	StartDate	PreSTartDate
    ------------------------------------------------------------------------------------------------
    13458	11146402	7	Closed	3	Review	2009-12-09 00:00:00.000	2008-04-03 00:00:00.000
    13458	11146402	7	Closed	9	PreClosed	2010-11-18 00:00:00.000	2010-11-18 00:00:00.000
    13458	11146402	7	Closed	3	Review	2016-06-13 00:00:00.000	2011-04-07 00:00:00.000
    13458	11146402	7	Closed	10	Completed	2018-02-18 00:00:00.000	2017-11-18 00:00:00.000
    
    
    
    
    
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • 답변으로 표시됨 Red987 2018년 6월 18일 월요일 오후 1:11
    2018년 6월 15일 금요일 오후 3:10

모든 응답

  • Hi Red987,

    This took quite sometime (I wish you had posted table creation and data insertion scripts). I added the related records in a group and then took their relative position to find the previous values. This works with your given sample data however, you may want to test this a bit more. Getting the records in correct order to the first temp table is important.

    CREATE TABLE Episode (StoreID bigint, PersonId bigint, TypeId int, TypeDesc varchar(20), CreatedAt datetime, StartDate datetime)
    INSERT INTO Episode
    VALUES 
    (13458,11146402,1,'Reported','12/18/2007  2:39:00 PM','12/18/2007'),
    (13458,11146402,6,'Accepted','12/19/2007 14:39','3/14/2008'),
    (13458,11146402,7,'Closed','12/20/2007 14:39','4/3/2008'),
    (13458,11146402,1,'Reported','12/21/2007 14:39','4/3/2008'),
    (13458,11146402,3,'Review','12/22/2007 14:39','4/3/2008'),
    (13458,11146402,7,'Closed','12/23/2007 14:39','12/9/2009'),
    (13458,11146402,1,'Reported','12/24/2007 14:39','2/3/2010'),
    (13458,11146402,3,'Review','12/25/2007 14:39','2/3/2010'),
    (13458,11146402,9,'PreClosed','12/26/2007 14:39','11/18/2010'),
    (13458,11146402,7,'Closed','12/27/2007 14:39','11/18/2010'),
    (13458,11146402,3,'Review','12/28/2007 14:39','4/7/2011'),
    (13458,11146402,7,'Closed','12/29/2007 14:39','6/13/2016'),
    (13458,11146402,1,'Reported','12/30/2007 14:39','2/3/2010'),
    (13458,11146402,3,'Review','12/31/2007 14:39','2/3/2017'),
    (13458,11146402,10,'Completed','1/1/2008 14:39','11/18/2017'),
    (13458,11146402,7,'Closed','1/2/2008 14:39','2/18/2018'),
    (13458,11146402,1,'Reported','1/3/2008 14:39','2/3/2010'),
    (13458,11146402,3,'Review','1/4/2008 14:39','2/3/2010'),
    (13458,11146402,7,'Closed','1/5/2008 14:39','2/3/2010')
    
    
    
    
    CREATE TABLE #TempEpisode (ID INT IDENTITY(1,1), StoreID bigint, PersonId bigint, TypeId int, TypeDesc varchar(20), StartDate datetime)
    INSERT INTO #TempEpisode (StoreID , PersonId , TypeId , TypeDesc , StartDate  )
    SELECT StoreID, PersonId, TypeId, TypeDesc ,StartDate	
    FROM Episode
    
    
    CREATE TABLE #EpisodeGroup (ID INT IDENTITY(1,1), StoreID bigint, PersonId bigint, TypeId int, TypeDesc varchar(20), StartDate datetime, GroupID int)
    DECLARE @i INT =1, @grp INT=1, @RecCount int=0
    SELECT @RecCount=COUNT(*) FROM #TempEpisode
    WHILE (@i<=@RecCount)
    BEGIN
    	INSERT INTO #EpisodeGroup (StoreID , PersonId , TypeId , TypeDesc , StartDate , GroupID)
    	SELECT StoreID , PersonId , TypeId , TypeDesc , StartDate , @grp
    	FROM #TempEpisode
    		WHERE ID = @i
        
    	IF (SELECT TypeId FROM #TempEpisode WHERE ID = @i)=7I 
    	   SET @grp=@grp+1	
    	SET @i = @i+1
    
    END
    
    SELECT StoreID , PersonId , TypeId , TypeDesc ,  PreTypeId , PreTypeDesc , StartDate ,PreStartDate
    FROM (
    SELECT StoreID , PersonId , TypeId , TypeDesc ,  StartDate , GroupID 
    ,LAG(TypeID, 1, NULL) OVER (PARTITION BY StoreID, PersonID ORDER BY StartDate) PreTypeId
    	,LAG(TypeDesc, 1, NULL) OVER (PARTITION BY StoreID, PersonID ORDER BY StartDate) PreTypeDesc
    	,LAG(StartDate, 1, NULL) OVER (PARTITION BY StoreID, PersonID ORDER BY StartDate) PreStartDate
    FROM #EpisodeGroup e
    )x 
    
    LEFT JOIN
    (
    SELECT GroupID, MIN(StartDate) StartTime, MAX(StartDate) EndTime , MIN(TypeID) MinTypeID
    FROM #EpisodeGroup
    GROUP BY GroupID
    HAVING DATEDIFF(hh,MIN(StartDate), MAX(StartDate))>0 
    ) y ON x.GroupID = y.GroupID 
    WHERE x.TypeDesc='Closed' AND x.PreTypeId IN (3,9,10)
    AND DATEDIFF(hh,StartTime, EndTime)>0
    
    DROP TABLE #TempEpisode,#EpisodeGroup
    DROP TABLE Episode
    
    /* OUTPUT
    StoreID              PersonId             TypeId      TypeDesc             PreTypeId   PreTypeDesc          StartDate               PreStartDate
    -------------------- -------------------- ----------- -------------------- ----------- -------------------- ----------------------- -----------------------
    13458                11146402             7           Closed               3           Review               2009-12-09 00:00:00.000 2008-04-03 00:00:00.000
    13458                11146402             7           Closed               9           PreClosed            2010-11-18 00:00:00.000 2010-11-18 00:00:00.000
    13458                11146402             7           Closed               3           Review               2016-06-13 00:00:00.000 2011-04-07 00:00:00.000
    13458                11146402             7           Closed               10          Completed            2018-02-18 00:00:00.000 2017-11-18 00:00:00.000
    
    */
    
    

     


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.


    2018년 6월 14일 목요일 오후 9:29
  • Thanks for putting time into this. Currently I'm using SQL Server 2008 where LAG function doesn't work.

    Thanks!

    Red

    2018년 6월 15일 금요일 오후 1:58
  • Why don't you mention it in your original post. Somebody put large effort to give solution and you re saying this is not version i am using.

    Saravanan ----------------------------------------------------------------------- Ever tried. Ever failed. No matter. Try Again. Fail again. Fail better.

    2018년 6월 15일 금요일 오후 2:05
  • Hi Red987,

    I wish you had mentioned that earlier. Unfortunately, I don't have much time today but you can learn about how to simulate LEAD/LAG function in various ways here: https://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    2018년 6월 15일 금요일 오후 2:35
  • Hi saravanatn1,

    Thanks for supporting me. I think I too should have checked that in the first place. Anyway, we all make mistakes and learn in the process.

    Thanks again,

    Ashish


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    2018년 6월 15일 금요일 오후 2:44
  • Here's the full illustration without using any 2012 only features

    CREATE TABLE #Episode (StoreID bigint, PersonId bigint, TypeId int, TypeDesc varchar(20), CreatedAt datetime, StartDate datetime)
    INSERT INTO #Episode
    VALUES 
    (13458,11146402,1,'Reported','12/18/2007  2:39:00 PM','12/18/2007'),
    (13458,11146402,6,'Accepted','12/19/2007 14:39','3/14/2008'),
    (13458,11146402,7,'Closed','12/20/2007 14:39','4/3/2008'),
    (13458,11146402,1,'Reported','12/21/2007 14:39','4/3/2008'),
    (13458,11146402,3,'Review','12/22/2007 14:39','4/3/2008'),
    (13458,11146402,7,'Closed','12/23/2007 14:39','12/9/2009'),
    (13458,11146402,1,'Reported','12/24/2007 14:39','2/3/2010'),
    (13458,11146402,3,'Review','12/25/2007 14:39','2/3/2010'),
    (13458,11146402,9,'PreClosed','12/26/2007 14:39','11/18/2010'),
    (13458,11146402,7,'Closed','12/27/2007 14:39','11/18/2010'),
    (13458,11146402,3,'Review','12/28/2007 14:39','4/7/2011'),
    (13458,11146402,7,'Closed','12/29/2007 14:39','6/13/2016'),
    (13458,11146402,1,'Reported','12/30/2007 14:39','2/3/2010'),
    (13458,11146402,3,'Review','12/31/2007 14:39','2/3/2017'),
    (13458,11146402,10,'Completed','1/1/2008 14:39','11/18/2017'),
    (13458,11146402,7,'Closed','1/2/2008 14:39','2/18/2018'),
    (13458,11146402,1,'Reported','1/3/2008 14:39','2/3/2010'),
    (13458,11146402,3,'Review','1/4/2008 14:39','2/3/2010'),
    (13458,11146402,7,'Closed','1/5/2008 14:39','2/3/2010')
    
    ;With CTE
    AS
    (
    select *,ROW_NUMBER() OVER (PARTITION BY StoreID,PersonId,TypeId ORDER BY CreatedAt) AS Seq
    from #Episode e
    WHERE TypeId = 7
    ),
    CTE2
    AS
    (
    SELECT  c1.*,c3.*
    FROM CTE c1
    LEFT JOIN CTE c2
    ON c2.Seq = c1.Seq -1
    AND c2.PersonId = c1.PersonId
    AND c2.StoreID = c1.StoreID
    CROSS APPLY
    (
    SELECT MIN(StartDate) AS GrpStart,
    MIN(CASE WHEN typeid = 1THEN StartDate END) AS Type1Date,
    MAX(CreatedAt) AS GrpEnd
    FROM #Episode
    WHERE StoreID = c1.StoreID
    AND PersonId = c1.PersonId
    AND CreatedAt < c1.CreatedAt
    AND (CreatedAt > c2.CreatedAt OR c2.CreatedAt IS NULL)
    )c3
    WHERE DATEADD(HH,24,GrpStart) < c1.StartDate
    OR Type1Date IS NULL
    )
    
    SELECT c.StoreID,c.PersonId,c.TypeId,c.TypeDesc,e.TypeId AS PreTypeID,e.TypeDesc AS PreType,c.StartDate,e.StartDate AS PreSTartDate
    FROM CTE2 c
    JOIN #Episode e
    ON e.StoreID = c.StoreID
    AND e.PersonID = e.PersonID
    AND e.CreatedAt = c.GrpEnd
    WHERE e.TypeId IN (3,9,10)
    ORDER BY StoreID,PersonId,StartDate
    
    
    
    
    /*
    Output
    -----------------------------------------------------------
    StoreID	PersonId	TypeId	TypeDesc	PreTypeID	PreType	StartDate	PreSTartDate
    ------------------------------------------------------------------------------------------------
    13458	11146402	7	Closed	3	Review	2009-12-09 00:00:00.000	2008-04-03 00:00:00.000
    13458	11146402	7	Closed	9	PreClosed	2010-11-18 00:00:00.000	2010-11-18 00:00:00.000
    13458	11146402	7	Closed	3	Review	2016-06-13 00:00:00.000	2011-04-07 00:00:00.000
    13458	11146402	7	Closed	10	Completed	2018-02-18 00:00:00.000	2017-11-18 00:00:00.000
    
    
    
    
    
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • 답변으로 표시됨 Red987 2018년 6월 18일 월요일 오후 1:11
    2018년 6월 15일 금요일 오후 3:10
  • /*
    Store ID        Person_id	type_id		Type	Pre_type_id	Pre_Type	start_date	Pre_start_date
    13458		11146402	7		Closed	3		Review		12/9/2009	4/3/2008
    13458		11146402	7		Closed	9		PreClosed	11/18/2010	11/18/2010
    13458		11146402	7		Closed	3		Review		6/13/2016	4/7/2011
    13458		11146402	7		Closed	10		Completed	02/18/2018	11/18/2017  
    */
    DECLARE	@Episode TABLE (Store_ID bigint, Person_ID bigint, TypeId int, TypeDesc varchar(20), CreatedAt datetime, StartDate datetime);
    INSERT	@Episode VALUES	(13458, 11146402,  1, 'Reported',  '12/18/2007  2:39:00 PM','12/18/2007'),
    			(13458, 11146402,  6, 'Accepted',  '12/19/2007 14:39','3/14/2008'),
    			(13458, 11146402,  7, 'Closed',    '12/20/2007 14:39','4/3/2008'),
    			(13458, 11146402,  1, 'Reported',  '12/21/2007 14:39','4/3/2008'),
    			(13458, 11146402,  3, 'Review',    '12/22/2007 14:39','4/3/2008'),
    			(13458, 11146402,  7, 'Closed',    '12/23/2007 14:39','12/9/2009'),
    			(13458, 11146402,  1, 'Reported',  '12/24/2007 14:39','2/3/2010'),
    			(13458, 11146402,  3, 'Review',    '12/25/2007 14:39','2/3/2010'),
    			(13458, 11146402,  9, 'PreClosed', '12/26/2007 14:39','11/18/2010'),
    			(13458, 11146402,  7, 'Closed',    '12/27/2007 14:39','11/18/2010'),
    			(13458, 11146402,  3, 'Review',    '12/28/2007 14:39','4/7/2011'),
    			(13458, 11146402,  7, 'Closed',    '12/29/2007 14:39','6/13/2016'),
    			(13458, 11146402,  1, 'Reported',  '12/30/2007 14:39','2/3/2010'),
    			(13458, 11146402,  3, 'Review',    '12/31/2007 14:39','2/3/2017'),
    			(13458, 11146402, 10, 'Completed', '1/1/2008   14:39','11/18/2017'),
    			(13458, 11146402,  7, 'Closed',    '1/2/2008   14:39','2/18/2018'),
    			(13458, 11146402,  1, 'Reported',  '1/3/2008   14:39','2/3/2010'),
    			(13458, 11146402,  3, 'Review',    '1/4/2008   14:39','2/3/2010'),
    			(13458, 11146402,  7, 'Closed',    '1/5/2008   14:39','2/3/2010');
    
    WITH cteSource(Store_ID, Person_ID, TypeID, Type, Start_Date, Created_At, grp)
    AS (
    	SELECT	e.Store_ID,
    		e.Person_ID,
    		e.TypeID,
    		e.TypeDesc AS Type,
    		e.StartDate,
    		e.CreatedAt,
    		ISNULL(SUM(CASE WHEN e.TypeID = 7 THEN 1 ELSE 0 END) OVER (PARTITION BY e.Store_ID, e.Person_ID ORDER BY e.CreatedAt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS grp
    	FROM	@Episode AS e
    ), cteLast(Store_ID, Person_ID, TypeID, Type, Start_Date, grp, rn)
    AS (
    	SELECT	x.Store_ID,
    		x.Person_ID,
    		x.TypeID,
    		x.Type,
    		x.Start_Date,
    		x.grp,
    		ROW_NUMBER() OVER (PARTITION BY x.Store_ID, x.Person_ID, x.grp ORDER BY x.Created_At DESC) AS rn
    	FROM	cteSource AS x
    ), cteGrouped(Store_ID, Person_ID, TypeID, Type, Pre_TypeID, Pre_Type, Start_Date, Pre_Start_Date, grp)
    AS (
    	SELECT		Store_ID,
    			Person_ID,
    			MAX(CASE WHEN rn = 1 THEN TypeID ELSE 0 END) AS TypeID,
    			MAX(CASE WHEN rn = 1 THEN Type ELSE NULL END) AS Type,
    			MAX(CASE WHEN rn = 2 THEN TypeID ELSE 0 END) AS Pre_TypeID,
    			MAX(CASE WHEN rn = 2 THEN Type ELSE NULL END) AS Pre_Type,
    			MAX(CASE WHEN rn = 1 THEN Start_Date ELSE NULL END) AS Start_Date,
    			MAX(CASE WHEN rn = 2 THEN Start_Date ELSE 0 END) AS Pre_Start_Date,
    			grp
    	FROM		cteLast
    	WHERE		rn <= 2
    	GROUP BY	Store_ID,
    			Person_ID,
    			grp
    )
    SELECT		Store_ID, 
    		Person_ID, 
    		TypeID, 
    		Type, 
    		Pre_TypeID, 
    		Pre_Type, 
    		Start_Date, 
    		Pre_Start_Date
    FROM		cteGrouped
    WHERE		CASE
    			WHEN Pre_TypeID = 3 AND Start_Date > Pre_Start_Date THEN 1
    			WHEN Pre_TypeID IN (9, 10) THEN 1
    			ELSE 0
    		END = 1
    ORDER BY	Store_ID, 
    		Person_ID,
    		grp;


    N 56°04'39.26"
    E 12°55'05.63"

    • 답변으로 제안됨 SwePesoMVP 2018년 6월 18일 월요일 오전 5:56
    2018년 6월 18일 월요일 오전 12:05
  • Hi Visakh,

    Thank you so much for your inputs. I do have one more scenario as listed below:

    Here I have the following fields Store ID, Person_id, type_id, Type, created_at, start_date.

    Store ID             Person_id           type_id              Type          created_at                                         start_date

    105871               10051214           Reported          1                2016-04-26 12:39:43.231899         2016-04-26

    105871               10051214           Completed       10             2016-04-26 12:39:58.948597         2016-04-26

    105871               10051214           Closed               7               2017-01-04 18:32:05.370045          2017-01-24

    116872               12250371           Reported          1              2018-02-28 19:34:28.114648          2018-02-28

    116872               12250371           Pre-Review      2               2018-03-13 19:24:35.394461          2018-03-06

    116872               12250371           Closed               7               2018-05-29 19:37:50.024231          2018-05-29

    116872               12250371           Reported          1               2018-06-02 21:30:57.717508          2018-06-02

    116872               12250371           Review              3              2018-06-07 14:52:52.189763           2018-06-07

    13458                1146402               Reported         1              2010-02-03 20:54:26                          2010-02-03

    13458                1146402               Review             3              2010-02-03 20:54:27                         2010-02-03

    13458                1146402               Closed              7              2010-11-18 20:54:07                         2010-11-18

    13458                1146402               Review             3              2011-04-07 13:02:30                         2011-04-07

    13458                1146402               Closed              7              2016-06-16 16:34:59.445614          2016-06-13

    Note:

    Same store I’d and same person I’d has different scenarios. Here I need all the records for example if it started in September, it would show up on the September report of records being started and also we need to delete if it starts type_id = 1, 2,3,10 and ended with type_id = 7 is within 24hrs.

    Note: Here we can’t take the minimum of type id because they are not assigned in orderly manner. Even we can’t take minimum of created date too because they can create all one time and backdate the start date to their respective occurrence.

    Final output:

    1) In month of April 2016 below record needs to be captured because it was started in 2016-04-26

    Store ID   Person_id    type_id   Type               Pre_type_id    Pre_Type    start_date      Pre_start_date

    105871    10051214    10            Completed           1                Reported     2016-04-26     2016-04-26

    2) In month of February 2018 below record needs to be captured because it was started in 2018-02-28

    Store ID   Person_id    type_id   Type                 Pre_type_id    Pre_Type    start_date      Pre_start_date

    116872    12250371      2            Pre-Review        1                      Reported    2018-03-06     2018-02-28

     

    3) In month of June 2018 below record needs to be captured because it was started in 2018-06-02

    Store ID   Person_id    type_id   Type               Pre_type_id    Pre_Type    start_date      Pre_start_date

    116872    12250371      3           Review             1                      Reported    2018-06-07       2018-06-02

     

    4) In month of February 2018 below record needs to be captured because it was started in 2010-02-03

    Store ID   Person_id    type_id   Type               Pre_type_id    Pre_Type    start_date      Pre_start_date

    13458      1146402             3           Review             1                    Reported     2010-02-03     2010-02-03

     

    5) In month of February 2018 below record needs to be captured because it was started in 2011-04-07

    Store ID   Person_id    type_id      Type               Pre_type_id    Pre_Type    start_date      Pre_start_date

    13458      1146402             3           Review             Null                  Null              2011-04-07    Null



    Red


    • 편집됨 Red987 2018년 6월 18일 월요일 오후 3:41
    2018년 6월 18일 월요일 오후 3:40
  • Please post the sample data and output in the same way I posted in my solution ie as CREATE insert statements

    Then i'll take a look


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 18일 월요일 오후 3:57
  • Please find the sample data:

    CREATE TABLE #Episode (StoreID bigint, PersonId bigint, TypeId int, TypeDesc varchar(20), CreatedAt datetime, StartDate datetime)
    INSERT INTO #Episode
    VALUES
    (105871,10051214,1,'Reported','2016-04-26 12:39:43.231899','2016-04-26'),
    (105871,10051214,10,'Completed','2016-04-26 12:39:58.948597','2016-04-26'),
    (105871,10051214,7,'Closed','2017-01-04 18:32:05.370045','2017-01-24'),
    (116872,12250371,1,'Reported','2018-02-28 19:34:28.114648','2018-02-28'),
    (116872,12250371,2,'Pre-Review','2018-03-13 19:24:35.394461','2018-03-06'),
    (116872,12250371,7,'Closed','2018-05-29 19:37:50.024231','2018-05-29'),
    (116872,12250371,1,'Reported','2018-06-02 21:30:57.717508','2018-06-02'),
    (116872,12250371,3,'Review','2018-06-07 14:52:52.189763','2018-06-07'),
    (13458,1146402,1,'Reported','2010-02-03 20:54:26','2010-02-03'),
    (13458,11146402,3,'Review','2010-02-03 20:54:27','2010-02-03'),
    (13458,11146402,7,'Closed','2010-11-18 20:54:07','2010-11-18'),
    (13458,11146402,3,'Review','2011-04-07 13:02:30','2011-04-07'),
    (13458,11146402,7,'Closed','2016-06-16 16:34:59.445614','2016-06-13')

    Thanks for looking into this!


    Red

    2018년 6월 18일 월요일 오후 4:09
  • Not fully clear

    DO you mean something like this?

    set dateformat ymd
    go
    DROP TABLE #Episode 
    CREATE TABLE #Episode (StoreID bigint, PersonId bigint, TypeId int, TypeDesc varchar(20), CreatedAt datetime2, StartDate datetime)
    INSERT INTO #Episode
    VALUES 
    (105871,10051214,1,'Reported','2016-04-26 12:39:43.231899','2016-04-26'),
    (105871,10051214,10,'Completed','2016-04-26 12:39:58.948597','2016-04-26'),
    (105871,10051214,7,'Closed','2017-01-04 18:32:05.370045','2017-01-24'),
    (116872,12250371,1,'Reported','2018-02-28 19:34:28.114648','2018-02-28'),
    (116872,12250371,2,'Pre-Review','2018-03-13 19:24:35.394461','2018-03-06'),
    (116872,12250371,7,'Closed','2018-05-29 19:37:50.024231','2018-05-29'),
    (116872,12250371,1,'Reported','2018-06-02 21:30:57.717508','2018-06-02'),
    (116872,12250371,3,'Review','2018-06-07 14:52:52.189763','2018-06-07'),
    (13458,11146402,1,'Reported','2018-02-03 20:54:26','2018-02-03'),
    (13458,11146402,3,'Review','2018-02-03 20:54:27','2018-02-03'),
    (13458,11146402,7,'Closed','2010-11-18 20:54:07','2010-11-18'),
    (13458,11146402,3,'Review','2011-04-07 13:02:30','2011-04-07'),
    (13458,11146402,7,'Closed','2016-06-16 16:34:59.445614','2016-06-13')
    
    
    DECLARE @dt date = '20180201'
    
    ;With CTE
    AS
    (
    SELECT *
    FROM #Episode e
    CROSS APPLY
    (
    SELECT MIN(StartDate) AS NxtClose
    FROM #Episode
    WHERE StoreID = e.StoreID
    AND PersonId = e.PersonId
    AND CreatedAt > = e.CreatedAt
    AND typeId = 7
    )e1
    ),
    CTE1
    AS
    (
    SELECT *
    FROM CTE c
    OUTER APPLY 
    (
    SELECT TOP 1 StartDate AS LastCreated,TypeDesc AS lastType
    FROM CTE
    WHERE StoreID = c.StoreID
    AND PersonId = c.PersonId
    AND COALESCE(NxtClose,'99991231') = COALESCE(c.NxtClose,'99991231')
    AND CreatedAt < c.CreatedAt
    ORDER BY CreatedAt DESC
    )c1
    )
    SELECT StoreID,PersonId,TypeId,TypeDesc,CreatedAt,StartDate,LastCreated AS PrevStartDate, LastType AS PrevType
    FROM CTE1
    WHERE @dt BETWEEN DATEADD(mm,DATEDIFF(mm,0,LastCreated),0) AND DATEADD(mm,DATEDIFF(mm,0,StartDate)+1,0)
    
    
    /*
    Output
    ----------------------------------------------------------
    StoreID	PersonId	TypeId	TypeDesc	CreatedAt	StartDate	PrevStartDate	PrevType
    -------------------------------------------------------------------------------------------------------
    116872	12250371	2	Pre-Review	2018-03-13 19:24:35.3944610	2018-03-06 00:00:00.000	2018-02-28 00:00:00.000	Reported
    13458	11146402	3	Review	2018-02-03 20:54:27.0000000	2018-02-03 00:00:00.000	2018-02-03 00:00:00.000	Reported
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 18일 월요일 오후 7:07
  • >> Here I have the following fields [sic; columns are not fields] Store_id emp_id, foobar_type, Type, creation_timestamp, start_date. <<

    Where is the DDL? I guess it's up to us to guess what the data types, keys and constraints are, and to correct your date and time formats to ISO standards. Don’t use your local dialect dates and a.m./p.m. on your times. Please learn ISO 8601 standards, if you’re going to stay in IT

    Also, if you have a start date, where is the terminal date? 

    Also, there is no such thing as a "foobar_type"; both those words are what the ISO standards call an "attribute property", which means they need to be assigned to an attribute. Do you think did you have a “blood_type” or a generic “type_id”? Why did fail to normalize this table? 

    Let's make some guesses and try and write the DDL that you wouldn't bother to post to us. 

    One of the goals of any database, not just RDBMS, is remove redundancy. Having those numeric codes for the type in the name of that status in the same row is a violation of third normal form. It also looks like your start date is drawn from the creation timestamp. Another redundancy! don’t store computed columns unless the computation is really elaborate. I’m going to guess that this is what you should have posted:

    CREATE TABLE Something_Reports
    (store_id CHAR(5) NOT NULL,
     emp_id CHAR(8) NOT NULL,
     CHECK(report_status IN ('Accepted', 'Closed', 'Preclosed','Reported', 'Reviewed', ‘Completed’))),
     event_timestamp DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
     PRIMARY KEY (store_id, emp_id, event_timestamp );

    INSERT INTO Something_Reports
    VALUES
    ('13458', '11146402', 'Accepted', ‘2008-03-14 19:15:00’),
    ('13458', '11146402', 'Closed', ‘2008-04-03 17:18:00’),
    ('13458', '11146402', 'Closed', ‘2009-12-09 05:00:00’),
    ('13458', '11146402', 'Closed', ‘2010-02-03 16:54:00’),
    ('13458', '11146402', 'Closed', ‘2016-06-16 16:34:00’),
    ('13458', '11146402', 'Closed', ‘2017-11-18 20:54:00’),
    ('13458', '11146402', 'Preclosed', ‘2010-11-18 20:54:00’),
    ('13458', '11146402', 'Reported', ‘2008-04-03 18:36:00’)
    ('13458', '11146402', 'Reported', ‘2010-02-03 08:54:00’),
    ('13458', '11146402', 'Reported', ‘2010-02-03 20:54:00’),
    ('13458', '11146402', 'Reviewed', ‘2008-04-03 18:36:00’),
    ('13458', '11146402', 'Reviewed', ‘2010-02-03 13:54:00’),
    ('13458', '11146402', 'Reviewed', ‘2010-02-03 20:54:00’),
    ('13458', '11146402', 'Reviewed', ‘2016-06-16 16:34:00’),
    ('13458', '11146402', 'Reviewed', ‘2017-11-18 20:54:00’),
    ('13458', '11146402', ‘Completed’, ‘2017-11-18 20:54:00’),
    (‘13458’, ‘11146402', 'Reported', ‘2007-12-18 14:39:00’);

    this merely takes your bad design inputs it into Third Normal Form and brings in a compliance with some ANSI/ISO standards. It’s still not a good way to do this. Please read this article

    https://www.red-gate.com/simple-talk/sql/t-sql-programming/state-transition-constraints/

    In SQL, most of the real work is not done in the DML like you’re trying to do it. It’s done in the DDL; this article will going to details as to how you can maintain a state transition from one status to the other in the DDL and not have to write those complicated queries. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    2018년 6월 18일 월요일 오후 10:20
  • Hi Visakh,

    Thanks for your inputs. The main logic needs to be written in such a way that how many reported in respective month and need to be captured data month by month. Need to delete if it starts type_id = 1, 2,3,10 and ended with type_id = 7 is within 24hrs. In below example data:

    116872               12250371           Reported          1              2018-02-28 19:34:28.114648          2018-02-28

    116872               12250371           Pre-Review      2               2018-03-13 19:24:35.394461          2018-03-06

    116872               12250371           Closed               7               2018-05-29 19:37:50.024231          2018-05-29

    116872               12250371           Reported          1               2018-06-02 21:30:57.717508          2018-06-02

    116872               12250371           Review              3              2018-06-07 14:52:52.189763           2018-06-07

    In above example first one it is reported in “2018-02-28” this needs to be captured in my February 2018 data an and again it reported on 2018-06-02 this needs to be captured in my June 2018 data as well as if there is other change of episode in same month then that should be current type and previous type will be “Reported’ one. There may be scenarios which may just start with type “Pre-review” or “Review” than starting with type “Reported”.

    Final output:

    --> In month of February 2018 below record needs to be captured because it was started in 2018-02-28

    Store ID   Person_id    type_id   Type             Pre_type_id    Pre_Type    start_date      Pre_start_date

    116872    12250371      1             Reported     Null                  Null           2018-02-28      Null

    --> In month of June 2018 below record needs to be captured because it was started in 2018-06-02

    Store ID   Person_id    type_id   Type               Pre_type_id    Pre_Type    start_date      Pre_start_date

    116872    12250371      3           Review             1                      Reported    2018-06-07       2018-06-02

    Note: The Closed type is not required in deriving the data for capturing the things that are started in respective month. Even they are closed they should have started in prior months.

    Basically I need to display the data which are started in respective months and need to check which type is present at end of that month. Usually it will start with type “Reported” i.e., type_id = 1 but there may be cases where it may be start directly with “Review” i.e., type_id = 3 or “Pre-Review” i.e., type_id = 2 than starting with type “Reported”

    Other Example:

    13458                1146402               Review             3              2011-04-07 13:02:30                         2011-04-07

    13458                1146402               Closed              7              2016-06-16 16:34:59.445614          2016-06-13

    Final output:

    In month of April 2011 below record needs to be captured because it was started in 2011-04-07

    Store ID   Person_id    type_id      Type               Pre_type_id    Pre_Type    start_date      Pre_start_date

    13458      1146402             3           Review             Null                  Null              2011-04-07    Null

    In above example it’s started with “Review” and by the end of same month there is no change in other episode so the records needs to be captured as

    Store ID   Person_id    type_id      Type               Pre_type_id    Pre_Type    start_date      Pre_start_date

    13458      1146402             3           Review             Null                  Null              2011-04-07    Null





    • 편집됨 Red987 2018년 6월 19일 화요일 오후 6:58
    2018년 6월 19일 화요일 오후 6:40