acrutchley (4/16/2015)
Hi Jeff,I did try the ISO_WEEK option but I was still getting overlaps with the months.... which caused duplicate week number entries because I was grouping by the month in the same query. Although there is probably a better option than running with two queries, I do get unique week numbers and months. Although week 53 was also a bit of an issue as datepart month returned this as Jan instead of December as I wished... simple update statement after the queries had run resolved this.... bit of a hack though!
Thanks for your help,
Adam
Sorry for the delay.
Ugh!. Ok. I get it. Heh... I hate these types of display requirements. 😉 And Jacob is correct. A calendar table would make this relatively easy to do. I just need to ask a couple of questions.
1. What is the first day of a week according to your company? Sunday? Monday? or ???
2. How do you want the months to be displayed for the month-overlap weeks? I know that you posted that above but just want to make sure. For example, can this be limited to just 3 letter months such as Mar/Apr?
3. Same question as number 2 above but for the last week of the year when you have a year overlap. How do you want THAT displayed? If you want Dec 2014/Jan 2015, do you also want such a format in #2 above so that the humans that want this done in the first place don't have to read left or right to figure out what year July is in? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.