June 4, 2013 at 9:23 am
I have been struggling with this all morning, and I think I am missing something obvious. What I need to do is take a typical date dim table and count every 7 days as a 1 week, keeping the running count, but at week 52 it needs to start back at 1.
This requirement does not match to the datepart function's weeks. I have tried several different ways, and I can get a count of weeks, which changes on the correct day, but I am stuck on getting the count to restart.
Can anyone point me in the right direction?
June 4, 2013 at 9:50 am
David.Lester (6/4/2013)
I have been struggling with this all morning, and I think I am missing something obvious. What I need to do is take a typical date dim table and count every 7 days as a 1 week, keeping the running count, but at week 52 it needs to start back at 1.This requirement does not match to the datepart function's weeks. I have tried several different ways, and I can get a count of weeks, which changes on the correct day, but I am stuck on getting the count to restart.
Can anyone point me in the right direction?
You can use modulus math for this.
case WeekNum when 52 then 52 else WeekNum % 52
If you don't do the case expression you will get 0 when it is week 52.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2013 at 12:22 pm
Thanks! I am using modulo to get to what I need. I just could not figure out how to in one set operation get the count to reset after 52.
I do have something working, and while it's a loop, it still completes in sub second for the amount of data I am going to end up having to deal with. I just think there has to be a better way.
I tried looking up a 4-4-5 Financial calendar, but the business has broken it's own rules, so I am having to find alternate methods.
June 4, 2013 at 12:48 pm
David.Lester (6/4/2013)
Thanks! I am using modulo to get to what I need. I just could not figure out how to in one set operation get the count to reset after 52.I do have something working, and while it's a loop, it still completes in sub second for the amount of data I am going to end up having to deal with. I just think there has to be a better way.
I tried looking up a 4-4-5 Financial calendar, but the business has broken it's own rules, so I am having to find alternate methods.
Glad that helped. If you want some help to kill the loop I will be happy to lend a hand.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2013 at 4:45 pm
David.Lester (6/4/2013)
I have been struggling with this all morning, and I think I am missing something obvious. What I need to do is take a typical date dim table and count every 7 days as a 1 week, keeping the running count, but at week 52 it needs to start back at 1.This requirement does not match to the datepart function's weeks. I have tried several different ways, and I can get a count of weeks, which changes on the correct day, but I am stuck on getting the count to restart.
Can anyone point me in the right direction?
What day of the week does your week start on and what do you want to do with the first and last week of the year which are partial weeks 6 out of 7 times.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 5, 2013 at 6:16 am
After digging into the data, and learning more about the history of this, it appears that the business has changed the logic of this about every other year, and even within one of those periods the choices were not consistent.
Result, I have a file being sent with the data needed. So I am good now on this.
However, to answer your question, they did 7 day weeks starting 1/1/2005, so there was not a specific day of week as the start of the week. It also ignored calendar year ends, 52 of the 7 day weeks counted as a year. So over time the period matched the calendar year less and less.
June 5, 2013 at 1:19 pm
David.Lester (6/5/2013)
After digging into the data, and learning more about the history of this, it appears that the business has changed the logic of this about every other year, and even within one of those periods the choices were not consistent.Result, I have a file being sent with the data needed. So I am good now on this.
However, to answer your question, they did 7 day weeks starting 1/1/2005, so there was not a specific day of week as the start of the week. It also ignored calendar year ends, 52 of the 7 day weeks counted as a year. So over time the period matched the calendar year less and less.
Ouch! Perhaps they could be convinced to us ISO weeks in the future?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 5, 2013 at 1:35 pm
Definitely ouch.
We have a large list of things to encourage the business to change, that is on it.
June 5, 2013 at 1:50 pm
WOW! I don't know if there's a way to recover from that. Given that all the accounting numbers would have to be redone for each week and month since 2005, it would be a herculean effort. This is a perfect example of something that should be so simple (ISO week) could have saved a lot of headache.
Keep that on your list of things to fix. Good luck with it.
June 6, 2013 at 5:58 am
Agreed!
However, I did just start here. I dragged the last place I was in from paper systems to nearly 2010. (It was 2012 when I got them to 2010, but hey, it was a huge jump.)
June 6, 2013 at 6:58 am
David.Lester (6/6/2013)
Agreed!However, I did just start here. I dragged the last place I was in from paper systems to nearly 2010. (It was 2012 when I got them to 2010, but hey, it was a huge jump.)
You werent working in the Insurance industry by any chance where you?? Based on observation, of some sectors of the Insurance industry, Ludites could be considered progressive. 😛
Seriously thats quite an interesting problem, one solution would be to have a field in the Calender Table that is populated using CEILING(datepart(dy,Date)/7.00).
Ignore this i've just seen the requirement to reset to 1 where week No >53, its still doable but a lot more effort than seans solution.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply