Search code examples
caseserial-numberdense-rank

Add serial number for each id based on dates

I have a dataset like shown below (except the Ser_NO, this is the field i want to create).

+--------+------------+--------+
| CaseID | Order_Date | Ser_No |
+--------+------------+--------+
|   44   | 22-01-2018 |   1    |
+--------+------------+--------+
|   44   | 24-02-2018 |   3    |
+--------+------------+--------+
|   44   | 12-02-2018 |   2    |
+--------+------------+--------+
|   100  | 24-01-2018 |   1    |
+--------+------------+--------+
|   100  | 26-01-2018 |   2    |
+--------+------------+--------+
|   100  | 27-01-2018 |   3    |
+--------+------------+--------+

How can i achieve a serial number for each CaseId based on my dates. So the first date in a specific CaseID gets number 1, the second date in this CaseID gets number 2 and so on. I'm working with T-SQL btw,

I've tried a few things:

CASE
WHEN COUNT(CaseID) > 1
THEN ORDER BY (Order_Date)
AND Ser_no +1
END

Thanks in advance.

Solution

  • First of all, although I don't understand what you did, it gives you what you wanted. The serial number is assigned by date order. The problem I can see is that the result shows you the rows in the wrong order (1, 3, 2 instead of 1, 2, 3).

    To sort that order you can try this:

    SELECT *, ROW_NUMBER() OVER (PARTITION BY caseid ORDER BY caseid, order_date) AS ser_no
FROM [Table]