CODEWITHSUNDEEP
sql-servert-sql
glass_kites
glass_kites

Reputation: 411

T-SQL Row_Number Condition/Case

I have the below data sample with a ROW_NUMBER function which has previously done the job for us.

+----+-------------------------+------+--------+------+-----+
| ID |       Start_Date        | Code | Serial | Type | Sqn |
+----+-------------------------+------+--------+------+-----+
| 03 | 2020-09-23 00:00:00.000 |  674 |   4388 | P    |   1 |
| 03 | 2020-09-24 00:00:00.000 |  654 |   4388 | T    |   1 |
| 03 | 2020-09-24 00:00:00.000 |  912 |   4388 | T    |   2 |
| 42 | 2019-08-23 00:00:00.000 |  671 |   1316 | P    |   1 |
| 42 | 2019-08-23 00:00:00.000 |  921 |   1316 | P    |   2 |
| 42 | 2019-08-27 00:00:00.000 |  671 |   1316 | P    |   1 |
| 42 | 2019-08-29 00:00:00.000 |  671 |   1316 | P    |   1 |
| 42 | 2019-08-29 00:00:00.000 |  921 |   1316 | P    |   2 |
| 42 | 2019-08-29 00:00:00.000 |  654 |   1316 | T    |   3 |
| 42 | 2019-08-29 00:00:00.000 |  911 |   1316 | T    |   4 |
| 51 | 2020-09-22 00:00:00.000 |  674 |   3210 | P    |   1 |
| 51 | 2020-09-22 00:00:00.000 |  654 |   3210 | T    |   2 |
| 51 | 2020-09-22 00:00:00.000 |  912 |   3210 | T    |   3 |
+----+-------------------------+------+--------+------+-----+

ROW_NUMBER () OVER (PARTITION BY Serial,[Start_Date] ORDER BY Type, Sqn) AS Sqn

However, due to a process change we need to handle certain records (674 + P on days prior) to get partitioned together, without affecting the others.

i.e. as below

+----+-------------------------+------+--------+------+-----+
| ID |       Start_Date        | Code | Serial | Type | Sqn |
+----+-------------------------+------+--------+------+-----+
| 03 | 2020-09-23 00:00:00.000 |  674 |   4388 | P    |   1 |
| 03 | 2020-09-24 00:00:00.000 |  654 |   4388 | T    |   2 |
| 03 | 2020-09-24 00:00:00.000 |  912 |   4388 | T    |   3 |
| 42 | 2019-08-23 00:00:00.000 |  671 |   1316 | P    |   1 |
| 42 | 2019-08-23 00:00:00.000 |  921 |   1316 | P    |   2 |
| 42 | 2019-08-27 00:00:00.000 |  671 |   1316 | P    |   1 |
| 42 | 2019-08-29 00:00:00.000 |  671 |   1316 | P    |   1 |
| 42 | 2019-08-29 00:00:00.000 |  921 |   1316 | P    |   2 |
| 42 | 2019-08-29 00:00:00.000 |  654 |   1316 | T    |   3 |
| 42 | 2019-08-29 00:00:00.000 |  911 |   1316 | T    |   4 |
| 51 | 2020-09-22 00:00:00.000 |  674 |   3210 | P    |   1 |
| 51 | 2020-09-22 00:00:00.000 |  654 |   3210 | T    |   2 |
| 51 | 2020-09-22 00:00:00.000 |  912 |   3210 | T    |   3 |
+----+-------------------------+------+--------+------+-----+

You can see the ID 03 Sqn is now 1,2,3 instead of 1,1,2.

I had considered adding a CASE WHEN into the ROW_NUMBER function, but couldn't quite crack it.

Upvotes: 0

Views: 60

Answers (1)

GarethD
GarethD

Reputation: 69749

Instead of partitioning by StartDate, you can modify it slightly to add one day to start day for the criteria you have set out, e.g.

DATEADD(DAY, CASE WHEN t.Type = 'P' AND Code = 674 THEN 1 ELSE 0 END, [Start_Date])

e.g.

DECLARE @T TABLE (ID INT, Start_Date DATETIME, Code INT, Serial INT, Type VARCHAR(1));

INSERT @T (ID, Start_Date, Code, Serial, Type)
VALUES
    (03, '2020-09-23 00:00:00', 674, 4388, 'P'),
    (03, '2020-09-24 00:00:00', 654, 4388, 'T'),
    (03, '2020-09-24 00:00:00', 912, 4388, 'T'),
    (42, '2019-08-23 00:00:00', 671, 1316, 'P'),
    (42, '2019-08-23 00:00:00', 921, 1316, 'P'),
    (42, '2019-08-27 00:00:00', 671, 1316, 'P'),
    (42, '2019-08-29 00:00:00', 671, 1316, 'P'),
    (42, '2019-08-29 00:00:00', 921, 1316, 'P'),
    (42, '2019-08-29 00:00:00', 654, 1316, 'T'),
    (42, '2019-08-29 00:00:00', 911, 1316, 'T'),
    (51, '2020-09-22 00:00:00', 674, 3210, 'P'),
    (51, '2020-09-22 00:00:00', 654, 3210, 'T'),
    (51, '2020-09-22 00:00:00', 912, 3210, 'T');


SELECT  *,
        ROW_NUMBER () OVER (PARTITION BY Serial, DATEADD(DAY, CASE WHEN t.Type = 'P' AND Code = 674 THEN 1 ELSE 0 END, [Start_Date]) ORDER BY Type, Code) AS Sqn
FROM @T AS t
ORDER BY ID

ADENDUM

The following appears to work as required:

SELECT  t.ID, t.Code, t.Serial, t.Type, t.Start_Date,
        ROW_NUMBER () OVER (PARTITION BY Serial,PartitionDate ORDER BY Type, T.Code) 
FROM    (   SELECT  t.ID, 
                    PartitionDate = CASE WHEN t.Code = 674 AND t.Type = 'P' AND 
                                            DATEDIFF(DAY, LEAD(t.Start_Date) OVER(PARTITION BY ID ORDER BY Type, Code), t.Start_Date) <= 1 
                                        THEN LEAD(t.Start_Date) OVER(PARTITION BY ID ORDER BY t.Start_Date, Type, Code)
                                    ELSE t.Start_Date
                                END,
                    t.Code, 
                    t.Serial, 
                    t.Type,
                    t.Start_Date
            FROM @T AS t
        ) AS t
ORDER BY ID, t.Start_Date, t.Type

This creates a new date in a subquery to partition by which is just the start_date, unless the code is 674 and the type is T and the next event for that ID is within 1 day (change as required), in which case it will use the next event date for that particular ID.

Upvotes: 1

Related Questions