Compare previous and next rows to derive output


  • Hello,
    Below is the SQL code for sample data.

    If Object_ID('tempdb..#Case','U') IS NOT NULL            
        Drop Table #Case        
                
    GO            
    CREATE TABLE #Case            
    (            
        Customer    Varchar(20),    
        TranID        Int,
        TranDate    Date,    
        Reason        Varchar(8),
        TranStatus    Char(1)    
    )
    GO
    INSERT INTO #Case (
        Customer
        ,TranID
        ,TranDate
        ,Reason
        ,TranStatus
        )
    Select 'Customer1',1,'19 Jan 2018','CHEMO','D' Union All
    Select 'Customer1',2,'20 Jan 2018','CHEMO','D' Union All
    Select 'Customer1',3,'21 Jan 2018','SPORT','A' Union All
    Select 'Customer1',4,'22 Jan 2018','CHEMO','D' Union All
    Select 'Customer2',1,'19 Jan 2018','CHEMO','A' Union All
    Select 'Customer2',2,'20 Jan 2018','CHEMO','D' Union All
    Select 'Customer2',3,'21 Jan 2018','CHEMO','A' Union All
    Select 'Customer2',4,'22 Jan 2018','SPORT','D' Union All
    Select 'Customer3',1,'19 Jan 2018','CHEMO','A' Union All
    Select 'Customer3',2,'20 Jan 2018','CHEMO','D' Union All
    Select 'Customer3',3,'21 Jan 2018','CHEMO','D' Union All
    Select 'Customer3',4,'22 Jan 2018','SPORT','D' Union All
    Select 'Customer4',1,'19 Jan 2018','CHEMO','D' Union All
    Select 'Customer4',2,'20 Jan 2018','CHEMO','D' Union All
    Select 'Customer4',3,'21 Jan 2018','SPORT','A' Union All
    Select 'Customer4',4,'22 Jan 2018','CHEMO','A' Union All
    Select 'Customer5',1,'19 Jan 2018','CHEMO','D' Union All
    Select 'Customer5',2,'20 Jan 2018','CHEMO','D' Union All
    Select 'Customer5',3,'21 Jan 2018','SPORT','D' Union All
    Select 'Customer5',4,'22 Jan 2018','CHEMO','D'

    The requirement is as below. 
    1. There are multiple transaction rows for a customer.
    2. Each transaction will generate a new record with new transaction date and transaction id.
    3. We want to pick all the record for those customer who satisfy any of the below criteria.
          a. All customers for whom all the transaction records have reason = "Chemo" and Status = "D"
          b. Customers for whom there is reason = "Chemo" and status = "D" followed by reason = "SPORT" and status = "A" as long as there is no subsequent record with reason = "Chemo" and status = "D"
          c. Customers for whom there is reason = "Chemo" and status = "A" followed by reason = "CHEMO" and status = "D" as long as there is no subsequent record with reason = <ANY> (It can be Chemo or Sport) and status = "A"
     

  • You can try below lines of code. I hope that it will work.
    ;WITH CTE_Main AS (

        SELECT
            Customer,
            TranID,
            TranDate,
            Reason,
            TranStatus,
            LEAD(Reason) OVER (partition by customer ORDER BY TranDate) AS LeadReason,
            LEAD(TranStatus) OVER (partition by customer ORDER BY TranDate) AS LeadStatus
        FROM #Case

    ), CTE_Case_1 AS (
            SELECT
                DISTINCT Customer
            FROM CTE_Main M
            WHERE (Reason = 'CHEMO' AND TranStatus = 'D' AND LeadReason = 'SPORT' AND LeadStatus = 'A')
                     AND NOT EXISTS(SELECT 1 FROM CTE_Main A WHERE A.Customer = M.Customer AND A.TranDate > M.TranDate AND A.Reason = 'CHEMO' AND A.TranStatus = 'D')
    ), CTE_Case_2 AS (
            SELECT
                DISTINCT Customer
            FROM CTE_Main M
            WHERE (Reason = 'CHEMO' AND TranStatus = 'A' AND LeadReason = 'CHEMO' AND LeadStatus = 'D')
          AND NOT EXISTS(SELECT 1 FROM CTE_Main A WHERE A.Customer = M.Customer AND A.TranDate > M.TranDate AND A.TranStatus = 'A')
    ), CTE_CASE_3 AS (
        SELECT
         Customer
        FROM #Case
        GROUP BY Customer
        HAVING MIN(Reason) = 'CHEMO' AND MIN(TranStatus) = 'D' AND MAX(TranStatus) = 'D' AND MAX(Reason) = 'CHEMO'
    )
    SELECT
        CTE_Main.Customer,
        TranID,
        TranDate,
        Reason,
        TranStatus
    FROM CTE_Main
    LEFT JOIN CTE_Case_1 ON CTE_Main.Customer = CTE_Case_1.Customer
    LEFT JOIN CTE_Case_2 ON CTE_Main.Customer = CTE_Case_2.Customer
    LEFT JOIN CTE_Case_3 ON CTE_Main.Customer = CTE_Case_3.Customer
    WHERE
    (CTE_Case_1.Customer IS NOT NULL OR CTE_Case_2.Customer IS NOT NULL OR CTE_Case_3.Customer IS NOT NULL)
    ORDER BY CTE_Main.Customer, TranDate

    Regards
    VG

  • Hi Vivek,

    Thank you for your quick response on the query and efforts. As shown in the diagram, we do not want customer 4 in the output.
    Why we do not want customer 4 : It has reason = "Chemo" and Status = "D" followed by reason = "Chemo" and Status = "A".

    Also we need Customer5 in the output as all the records have reason = "Chemo" and Status = "D".

    Regards,
    Akash

  • GroverVivek - Friday, January 19, 2018 6:28 AM

    You can try below lines of code. I hope that it will work.
    ;WITH CTE_Main AS (

        SELECT
            Customer,
            TranID,
            TranDate,
            Reason,
            TranStatus,
            LEAD(Reason) OVER (partition by customer ORDER BY TranDate) AS LeadReason,
            LEAD(TranStatus) OVER (partition by customer ORDER BY TranDate) AS LeadStatus
        FROM #Case

    ), CTE_Case_1 AS (
            SELECT
                DISTINCT Customer
            FROM CTE_Main M
            WHERE (Reason = 'CHEMO' AND TranStatus = 'D' AND LeadReason = 'SPORT' AND LeadStatus = 'A')
                     AND NOT EXISTS(SELECT 1 FROM CTE_Main A WHERE A.Customer = M.Customer AND A.TranDate > M.TranDate AND A.Reason = 'CHEMO' AND A.TranStatus = 'D')
    ), CTE_Case_2 AS (
            SELECT
                DISTINCT Customer
            FROM CTE_Main M
            WHERE (Reason = 'CHEMO' AND TranStatus = 'A' AND LeadReason = 'CHEMO' AND LeadStatus = 'D')
          AND NOT EXISTS(SELECT 1 FROM CTE_Main A WHERE A.Customer = M.Customer AND A.TranDate > M.TranDate AND A.TranStatus = 'A')
    ), CTE_CASE_3 AS (
        SELECT
         Customer
        FROM #Case
        GROUP BY Customer
        HAVING MIN(Reason) = 'CHEMO' AND MIN(TranStatus) = 'D' AND MAX(TranStatus) = 'D' AND MAX(Reason) = 'CHEMO'
    )
    SELECT
        CTE_Main.Customer,
        TranID,
        TranDate,
        Reason,
        TranStatus
    FROM CTE_Main
    LEFT JOIN CTE_Case_1 ON CTE_Main.Customer = CTE_Case_1.Customer
    LEFT JOIN CTE_Case_2 ON CTE_Main.Customer = CTE_Case_2.Customer
    LEFT JOIN CTE_Case_3 ON CTE_Main.Customer = CTE_Case_3.Customer
    WHERE
    (CTE_Case_1.Customer IS NOT NULL OR CTE_Case_2.Customer IS NOT NULL OR CTE_Case_3.Customer IS NOT NULL)
    ORDER BY CTE_Main.Customer, TranDate

    Hi Vivek,

    Thank you for your quick response on the query and efforts. As shown in the diagram, we do not want customer 4 in the output.
    Why we do not want customer 4 : It has reason = "Chemo" and Status = "D" followed by reason = "Chemo" and Status = "A".

    Also we need Customer5 in the output as all the records have reason = "Chemo" and Status = "D".

    Regards,
    Akash

  • akash_singh - Friday, January 19, 2018 7:27 AM

    Hi Vivek,

    Thank you for your quick response on the query and efforts. As shown in the diagram, we do not want customer 4 in the output.
    Why we do not want customer 4 : It has reason = "Chemo" and Status = "D" followed by reason = "Chemo" and Status = "A".

    Also we need Customer5 in the output as all the records have reason = "Chemo" and Status = "D".

    Regards,
    Akash

    Customer5 has one record with "Sport"/"D", and does not qualify as having all "Chemo"/"D".

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • How about this?  It's probably possible to tidy it up so it only does one table scan, instead of two, but I'm afraid I ran out of time

    WITH ReasonsandStatuses AS (
        SELECT
             Customer
        ,    TranID 
        ,    TranDate
        ,    Reason 
        ,    TranStatus
        ,    CASE WHEN Reason <> 'CHEMO' OR TranStatus <> 'D' THEN 0 ELSE TranID END AS ChemoD
        ,    CASE WHEN Reason <> 'SPORT' OR TranStatus <> 'A' THEN 0 ELSE TranID END AS SportA
        ,    CASE WHEN Reason <> 'CHEMO' OR TranStatus <> 'A' THEN 0 ELSE TranID END AS ChemoA
        ,    CASE WHEN TranStatus = 'A' THEN TranID ELSE 0 END AS A
        FROM #Case
        )
    , AreTheresubsequentReasonsandStatuses AS (
        SELECT
            Customer
        ,    TranID 
        ,    TranDate
        ,    Reason 
        ,    TranStatus
        ,    ChemoD
        ,    SportA
        ,    ChemoA
        ,    A
        ,    COALESCE(MIN(ChemoD) OVER (PARTITION BY Customer ORDER BY TranID ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0) AS MinChemoD
        ,    COALESCE(MAX(ChemoD) OVER (PARTITION BY Customer ORDER BY TranID ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0) AS MaxChemoD
        ,    COALESCE(MIN(SportA) OVER (PARTITION BY Customer ORDER BY TranID ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0) AS MinSportA
        ,    COALESCE(MAX(SportA) OVER (PARTITION BY Customer ORDER BY TranID ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0) AS MaxSportA
        ,    COALESCE(MAX(A)       OVER (PARTITION BY Customer ORDER BY TranID ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0) AS MaxA
        FROM ReasonsandStatuses
        )
    SELECT
         x.Customer
    ,    x.TranID
    ,    x.TranDate
    ,    x.Reason
    ,    x.TranStatus
    FROM AreTheresubsequentReasonsandStatuses a
    CROSS APPLY ( -- to make sure we capture all the rows for a customer
        SELECT
             Customer
        ,    TranID
        ,    TranDate
        ,    Reason
        ,    TranStatus
        FROM AreTheresubsequentReasonsandStatuses
        WHERE Customer = a.Customer
        ) x
    WHERE (a.TranID = 1 AND a.MinChemoD > 1 AND a.MaxChemoD > 1) -- criterion a
    OR (a.ChemoD > 0 AND a.MinSportA > 0 AND a.MaxChemoD < a.MaxSportA) -- criterion b
    OR (a.ChemoA > 0 AND a.MaxChemoD > 0 AND a.MaxA < a.MaxChemoD) -- criterion c

    John

  • drew.allen - Friday, January 19, 2018 8:48 AM

    akash_singh - Friday, January 19, 2018 7:27 AM

    Hi Vivek,

    Thank you for your quick response on the query and efforts. As shown in the diagram, we do not want customer 4 in the output.
    Why we do not want customer 4 : It has reason = "Chemo" and Status = "D" followed by reason = "Chemo" and Status = "A".

    Also we need Customer5 in the output as all the records have reason = "Chemo" and Status = "D".

    Regards,
    Akash

    Customer5 has one record with "Sport"/"D", and does not qualify as having all "Chemo"/"D".

    Drew

    Adding one more thing:
    Customer 4 satisfies following point
    b. Customers for whom there is reason = "Chemo" and status = "D" followed by reason = "SPORT" and status = "A" as long as there is no subsequent record with reason = "Chemo" and status = "D"

    Regards
    VG

  • GroverVivek - Saturday, January 20, 2018 1:15 AM

    drew.allen - Friday, January 19, 2018 8:48 AM

    akash_singh - Friday, January 19, 2018 7:27 AM

    Hi Vivek,

    Thank you for your quick response on the query and efforts. As shown in the diagram, we do not want customer 4 in the output.
    Why we do not want customer 4 : It has reason = "Chemo" and Status = "D" followed by reason = "Chemo" and Status = "A".

    Also we need Customer5 in the output as all the records have reason = "Chemo" and Status = "D".

    Regards,
    Akash

    Customer5 has one record with "Sport"/"D", and does not qualify as having all "Chemo"/"D".

    Drew

    Adding one more thing:
    Customer 4 satisfies following point
    b. Customers for whom there is reason = "Chemo" and status = "D" followed by reason = "SPORT" and status = "A" as long as there is no subsequent record with reason = "Chemo" and status = "D"

    No it doesn't. It only satisfies the first part of the condition "followed by reason = "SPORT"".  It fails the condition "and status = "A"".

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply