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
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
ASKER
It needs to be across years.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could do something like this:
PRINT CEILING(DATEPART(WEEK, GETDATE()) / 8.0)