CODEWITHSUNDEEP
sql-server-2012
staq
staq

Reputation: 163

sql server summarise or group data

I have an extract in SQL and I would like to group / Rank data in the format as shown in the table below in the desired output column. My question is how do to i achive this. I have tried Rank and Dense rank function but it is not giving me the desired result.

Cusomter_Id| Rno | Start_Dt | End_Dt | PM_Status|Desired_Output
-------------------------------------------------------------------------
1609 |1 |02/10/2014 |08/10/2014 |VD |1
-------------------------------------------------------------------------
1609 |2 |09/10/2014 |02/11/2014 |CC |2
-------------------------------------------------------------------------
1609 |3 |03/11/2014 |04/11/2014 |CC |2
-------------------------------------------------------------------------
1609 |4 |05/11/2014 |27/01/2015 |FD |3
-------------------------------------------------------------------------
1609 |5 |28/01/2015 |02/02/2015 |CC |4
-------------------------------------------------------------------------
1609 |6 |03/02/2015 |09/02/2015 |CC |4
-------------------------------------------------------------------------
1609 |7 |10/02/2015 |31/12/9999 |CC |4
-------------------------------------------------------------------------

I hope the above data make sense any questions please let me know.

Many thanks for looking inot my query.

Upvotes: 1

Views: 32

Answers (1)

Jason W
Jason W

Reputation: 13179

You can use a recursive CTE to find your grouping and achieve your desired results:

DECLARE @Data TABLE (
    Customer_Id INT,
    Rno INT,
    Start_Dt DATE,
    End_Dt DATE,
    PM_Status VARCHAR(5),
    Desired INT
)
INSERT @Data VALUES
    (1609, 1, '2/10/2014', '10/8/2014', 'VD', 1),
    (1609, 2, '10/9/2014', '11/2/2014', 'CC', 2),
    (1609, 3, '11/3/2014', '11/4/2014', 'CC', 2),
    (1609, 4, '11/5/2014', '1/27/2014', 'FD', 3),
    (1609, 5, '1/28/2015', '2/2/2015', 'CC', 4),
    (1609, 6, '2/3/2015', '2/9/2015', 'CC', 4),
    (1609, 7, '2/10/2015', NULL, 'CC', 4)

; WITH CTE AS (
    SELECT
        Customer_id,
        Rno,
        Start_Dt,
        End_Dt,
        PM_Status,
        Desired,
        1 AS Actual
    FROM @Data
    WHERE RNo = 1
    UNION ALL
    SELECT
        SRC.Customer_id,
        SRC.Rno,
        SRC.Start_Dt,
        SRC.End_Dt,
        SRC.PM_Status,
        SRC.Desired,
        CASE WHEN CTE.PM_Status <> SRC.PM_Status 
            THEN CTE.Actual + 1 ELSE CTE.Actual END
    FROM CTE
        INNER JOIN @Data SRC
            ON SRC.Customer_Id = CTE.Customer_Id
                AND SRC.Rno = CTE.Rno + 1
)
SELECT *
FROM CTE

Output:

Customer_id Rno         Start_Dt   End_Dt     PM_Status Desired     Actual
----------- ----------- ---------- ---------- --------- ----------- -----------
1609        1           2014-02-10 2014-10-08 VD        1           1
1609        2           2014-10-09 2014-11-02 CC        2           2
1609        3           2014-11-03 2014-11-04 CC        2           2
1609        4           2014-11-05 2014-01-27 FD        3           3
1609        5           2015-01-28 2015-02-02 CC        4           4
1609        6           2015-02-03 2015-02-09 CC        4           4
1609        7           2015-02-10 NULL       CC        4           4

Upvotes: 1

Related Questions