1

Using a single table with a reference and date column example below, how could I produce the out below to split the row number. The same reference on the same day should show as the same row number.

example below; 

MAINFRAJOB  SyncDate    Row Number
7861    02/10/2019  1
7861    02/10/2019  1
7861    03/10/2019  2
1045679 25/09/2019  1
10233649    03/10/2019  1
10233652    04/10/2019  1
10233652    04/10/2019  1
10233652    06/10/2019  2
123456789   02/10/2019  1
123456789   02/10/2019  1
123456789   03/10/2019  2
123456789   04/10/2019  3

I have tried this but it is not producing the correct results;

ROW_NUMBER()over(partition by cast(ard.SyncDate as date), ard.actionref order by cast(ard.SyncDate as date) desc) AS 'RowNo'

Thanks for any guidance.

user3306489
  • 139
  • 1
  • 7

2 Answers2

1

I think you are really looking for Dense_Rank() as BarneyL mentioned, but you also want to partition by MAINFRAJOB

Example

Select *
      ,Row_Number = DENSE_RANK() over (Partition By [MAINFRAJOB] Order by [SyncDate])
 From YourTable

Returns

enter image description here

John Cappelletti
  • 63,100
  • 6
  • 36
  • 57
0

Try DENSE_RANK instead, you also need to remove the date from the partition otherwise it resets to 1 each date change:

DENSE_RANK()over(partition by cast(ard.SyncDate as date), ard.actionref order by cast(ard.SyncDate as date) desc) AS 'RowNo'
BarneyL
  • 1,206
  • 8
  • 13