0

I have a stored procedure that creates a calendar table that spans many years for my financial reports, example the years run from 01/06/2015 to 31/05/2016. I want to change the weeknumber calculation so that it starts counting the weeks from the 01/06/2016

I found some code that I modified that nearly works but the first week on June is classed as 0 and not 1

DATEDIFF(week
        ,DATEADD(YEAR
                ,DATEDIFF(MONTH
                         ,'19000601'
                         ,@StartDate
                         ) / 12
                ,'19000601'
                )
        ,dateadd(d
                ,6 - datepart(w
                             ,cast(year(@StartDate)
                                   - case when month(@StartDate) < 6
                                          then 1
                                          else 0
                                          end
                                   as char(4)
                                  )
                              +'0601'
                             )
                ,@StartDate
                )
        ) AS WeekNum,

and

datediff(d
        ,cast(year(@StartDate) - case when month(@StartDate) < 6
                                      then 1
                                      else 0
                                      end
              as char(4)
             )
         + '0601'
        ,@StartDate
        ) / 7 AS WeekNum,
iamdave
  • 12,023
  • 3
  • 24
  • 53
w00t
  • 29
  • 7
  • Do you want the first day of your reporting year to be the start of the week? If not, how would you handle the first day of your reporting year being a Thursday, for example? – iamdave Feb 10 '17 at 09:55
  • The first day of the reporting week should be a Tuesday I did use a SET DATEFIRST 2 at the top of the procedure but it's not fully working. – w00t Feb 10 '17 at 10:14
  • so that means that : 2015-06-01 is week 1 and 2015-06-02 is week 2 with those dates? – Tanner Feb 10 '17 at 10:19
  • Yes that's correct, then in 2016 week 2 begins 2016-06-07 – w00t Feb 10 '17 at 10:24
  • Can you post the rest of the stored procedure? – iamdave Feb 10 '17 at 10:42
  • It's a pretty meaty one that someone I worked with created so I've pasted it at the following http://pastebin.com/k0VQ4rTE – w00t Feb 10 '17 at 11:04
  • Does this need to match across your other columns? So would the day 6 days after your start of the week per your question (In this case `01/06/2015 + 6 = 07/06/2015`) have a `IsEndOfWeekFlag` set or not? Or is that field reserved for Sundays? – iamdave Feb 10 '17 at 12:13
  • Oh, and you also have the 2016 New Year's Day Bank Holiday labelled as `Good Friday` – iamdave Feb 10 '17 at 12:16
  • It also appears that your Financial Years start in February, based on your `FinancialYear` and `FinancialMonth` column? Why in your question is the financial year now starting in June? – iamdave Feb 10 '17 at 12:29
  • It s a stored procedure that's been modified so ignore all the other columns I joining RealDate and using WeekNum, WeekStartDate & WeekEndDate, the rest is not needed for these reports. WeekStartDate & WeekEndDate work fine – w00t Feb 10 '17 at 13:20

0 Answers0