question

JamesB avatar image
JamesB asked

DENSE_RANK ranking count problem

[link text][1]Good day, I am trying to a dd a column to our calendar tally table,with the week of the month number. I am using DENSE_RANK() OVER(PARTITION BY [ISO_Month#] ORDER BY [ISO_Week#]) AS '__Week_of_Month__'. the problem is that in some cases, the ranking starts at 2 and not 1. I add a screencapture and test dataset in order to assist with replication of the issue. Using SQL 2016 SP1 with CU4 on Windows 10(1703) fully updated. ![alt text][2] ================================================== Dataset for testing: [link text][3] ================================================== --- My query: SELECT [Date] , [Year] , [MonthName] , [ISO_Month#] --- The month in which the full week (Monday to Friday) reside, Decier for week is where the THursday reside as per ISO8601 , [ISO_Week#] --- The standard ISO8601 week number , DENSE_RANK() OVER(PARTITION BY [ISO_Month#] ORDER BY [ISO_Week#]) AS '__Week_of_Month__' , [DayName] , [ISO_Day#] , [OrdinalDate] , [WeekStart] , [WeekEnd] , [FullWkNotation] FROM [cdata].[tb_ISOCalendar] ORDER BY [Date] ================================================== [1]: /storage/temp/4242-denserankdata-2.zip [2]: /storage/temp/4241-denserankissue.png [3]: /storage/temp/4240-denserankdata.zip
rankcalendartally
denserankissue.png (51.4 KiB)
denserankdata-2.zip (156.7 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mmm, I see if I use [WeekStart] in the DENSE_RANK's ORDER BY, it works differently. It many cases it counts correctly, (as in the case of the screencapture dates) but then in some years it count upto 10 or 11 weeks, before starting to count per month again.
0 Likes 0 ·

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
You need to partition by both year and isomonth to get the week_in_month calculation correct.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@JamesB As @Magnus Ahlkvist pointed out in his answer, the partition by clause needs to include the year column, but ***not*** the calendar year of course. You should have the column in the table which stores the ISO Year values (364 or 371 rows per each ISO year worth of dates), this is the column which needs to be included. Alternatively, if you already have a column for day number of month (running 1 through 28 or 1 through 35, depending on the month) then you can use just that column to calculate desired **Week\_of\_Month** values, without using any windowing functions at all, i.e. (([ISO_Day_of_Month#] - 1) / 7) + 1 as Week_of_Month The statement above utilizes the integer division of the whole numbers which simply strips the decimal part and returns the floor of the result. It can be used if the column for day number of month is already available.
2 Likes 2 ·
Can not remember if I tested with that combination in the many iterations I ran before I asked the question. Will test and revert, but is DENSE_RANK not supposed to give back consecutive numbers?
0 Likes 0 ·
It is. But since there if february 4th is iso_week 5 in any of the years in the result set, that will get dense_rank=1 if you partition only by month number. If you add year to the partition by-clause, you'll get a rank per year and month, instead of a rank of iso-weeks per month over all the years.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.