I am trying to wirte a sales report. Take today 24/06/2009 which is wednesday. I want to get the sales for this week so I will be adding up the sales from sunday to today (wednesday).
However I want to compare this to this week last year but only up to wednesday.
Can take todays date and minus 364 with datediff which gives me wednesday 25/06/2008. This is great but will it work for a leap year, I have tested it and it seems to work
leap years will cause you one problem, the other is going to be what to do when the year end is during the week.
I haven’t checked the dates, so I dont know when this will happen…
Lets say Jan 1st is a Friday. Which week’s numbers do you want to accumulate that to. does it count as week 1 of this year, or week 53 of last year.
Retailers have this problem all the time with Easter and Xmas, because Easter can fall into Q1 or Q2 which makes a big difference to turnover, and the run up to Xmas makes a big difference when the last weekend is before it. e.g. if the 23rd December is a Saturday, then pre Xmas figures can look dismal as everyone will leave the shopping till the last saturday (or it may look fantastic because there are three weekends in December before Xmas - whereas there would normally only be 2 - It depends on how you do the measuring)
I would suggest an approach whereby you find the week number in the year of the date, then find the Sunday of the same week number for last year and do a comparison based upon that. You will probably need to programme exceptional cases for Week1/Week53 validation…