-1

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.

miltenburger
  • 91
  • 6

2 Answers2

0

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]
Oram
  • 1,589
  • 2
  • 16
  • 22
0

Thanks for your reply, Sorry for the misunderstanding, because the ser_no is not yet in my table. That is the field a want to calculate.

I finished it myself this morning, but it looks almost the same like your measure:

RANK() OVER(PARTITION BY CaseID ORDER BY CaseID, Order_Date ASC

miltenburger
  • 91
  • 6