SQL Server: Create sequence column based on a non-distinct column

user_11568507 0
Viewed 24

I'm not sure if I'm asking this question right, but hopefully I can explain it well enough. I have a table that has a Date, Value, and WeekEndDate column. I want to create a sequence column that counts the distinct weeks from 1-13 and cycles every 13 weeks.

I attached a small sample of the output I'm trying to create. Is this even possible?

Desired Table Output

tsql sql-server sql
1 Answers

Use dense_rank() and some arithmetic:

select t.*,
       ((dense_rank() over (order by weekEnd) - 1) % 13) + 1
from t;
user_11440350
Related