I don't remember the specifics of the project where I did this but it was something like the week always begins on Saturday and all days in the week belong to that week even if they cross into the next month or even the next year. What if you need the Week to run from Sat to Fri? What would need to be altered to accommodate for that? Populate the ***ulativeWeek column in the date dimension: DECLARE DATETIME SELECT = MIN(FullDate) FROM dim_Date WHERE WeekDayName = 'Sunday' ĭECLARE DATETIME SELECT = MIN(FullDate) FROM dim_Date WHERE WeekDayName = 'Saturday'
Here is the first part of the script at 4. The logic used to assign the ***ulative week number is to get the minimum date for the first day of the week e.g.
Monday, Febru11:28:06 AM - Raymond Barley Which is quite fine and acceptable way to number weeks to me. This way I don't have 201501 week in my system, and weeks order would be 201453, 201502.
#SQL WEEK NUMBER FULL#
To explain this solution: I intended to change first week of year if it didn't have 7 days, I changed it to be the same as last week of previous year.įor example my weeks number would be as follows 201501, and since week 201501 wasn't full seven days, then I change it to 201453. I need to have a uinque week number in system, so I updated the table as follows: Now this solution has the problem of weeks spanning over two years. I saved the week number as follows, assuming I have and values: I think having week number in year is good to keep. I guess it's a bit late to comment here, but I have another approach.
0 kommentar(er)
