Link to home
Start Free TrialLog in
Avatar of Ryan Simmons
Ryan Simmons

asked on

How can I assign a unique value to each 8 week block in SQL?

I have the following table:

WeekEndDate      WeekNumber      WeekNumSeq
1/5/2019      1      1
1/12/2019      2      2
1/19/2019      3      3
1/26/2019      4      4
2/2/2019      5      5
2/9/2019      6      6
2/16/2019      7      7
2/23/2019      8      8
3/2/2019      9      1
3/9/2019      10      2
3/16/2019      11      3
3/23/2019      12      4
3/30/2019      13      5
4/6/2019      14      6
4/13/2019      15      7
4/20/2019      16      8
4/27/2019      17      1


How can I get SQL to assign a unique value to each 8 week block like this:
WeekEndDate      WeekNumber      WeekNumSeq      UniqueID
1/5/2019      1      1      1
1/12/2019      2      2      1
1/19/2019      3      3      1
1/26/2019      4      4      1
2/2/2019      5      5      1
2/9/2019      6      6      1
2/16/2019      7      7      1
2/23/2019      8      8      1
3/2/2019      9      1      2
3/9/2019      10      2      2
3/16/2019      11      3      2
3/23/2019      12      4      2
3/30/2019      13      5      2
4/6/2019      14      6      2
4/13/2019      15      7      2
4/20/2019      16      8      2
4/27/2019      17      1      3
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Does it need to be unique across years or just within a year?

You could do something like this:

PRINT CEILING(DATEPART(WEEK, GETDATE()) / 8.0)
Avatar of Ryan Simmons
Ryan Simmons

ASKER

It needs to be across years.
ASKER CERTIFIED SOLUTION
Avatar of Ryan Simmons
Ryan Simmons

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial