CODEWITHSUNDEEP
sqlsql-server
Craig
Craig

Reputation: 18734

Getting Week Number from list of dates

I have a view, which returns me a list of dates for a period of work we do. The dates are usually continuous. One of the columns is 'WeekNumber', which is based on the date values, and returns the week number of the year. The date column repeats as there are certain items which happens a few times per day.

What I want to add is a new column, called WeekNumberOfPeriod. which starts at 1 for each period in the view. So, if I get a result set with:

WeekNumber:
37
37
37
37
37
37
37
38
38
38
38
38
38
38
39
39
39
40
40
40
40
40

Then I want:

WeekNumber,WeekOfPeriod

37,1
37,1
37,1
37,1
37,1
37,1
37,1
38,2
38,2
38,2
38,2
38,2
38,2
38,2
39,3
39,3
39,3
40,4
40,4
40,4
40,4
40,4

I think I need Row_Number, but I am battling with what to partition by. I have a few projectss running, so it needs to somehow group by projectId, weeknumber.

I was hoping it was this:

    ROW_NUMBER() OVER(PARTITION BY pd.WeekNumber ORDER BY pd.DateValue) AS WeekNumberOfSprint,

But that just counted from 1 to x for each week number. I need the same week numbers to be `, then 2...

Upvotes: 2

Views: 123

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280625

Add DENSE_RANK to the outer SELECT list:

WeekOfPeriod = DENSE_RANK() OVER (ORDER BY pd.WeekNumber)

Upvotes: 4

Related Questions