Hello:

Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:

Jan - 1 - 27

Feb - 28 - 24

Mar - 25 - 31

Currently, I have an SSIS package creating a record for each day in the Time Dimension.

Is there any T-SQL script out there that will help me build a Fiscal calendar such as the one described above?

Thank you!

Hi desibull,

this only way i though it use "dateadd" function for your issue.

checkthe sample code as below:

decalre @dt_startdate datetime

set @dt_startdate = '2007-01-01'

select dateadd(day,0,@dt_startdate),dateadd(day,27,@dt_startdate),

dateadd(day,28,@dt_startdate),dateadd(day,55,@dt_startdate),

dateadd(day,56,@dt_startdate),dateadd(day,85,@dt_startdate),

'next_startdate'=dateadd(day,86@dt_startdate)

use this method for build date list.

take 'next_startdate' replace the @dt_startdate.

hoping this can help you.

Best Regrads,

Hunt.

Excel is a quick and easy way to create date/time dimensions. You can then import them into sql server using ssis.

The funky thing here is that 445, 445, 445, 445 (for quarters) leaves a couple days at the end of a year. It's three 91-day quarters and 1 93-day quarter.

Here's the query i came up with. Nothing succedes like brute force!

You can set any date for @dtFiscalYearStart and this query will work...that's the only variable. no tables needed for this...just run it.

Code Snippet

declare @dtFiscalYearStart smalldatetime

, @dtFiscalYearEnd smalldatetime

, @iDaysInFiscalYear smallint

set @dtFiscalYEarStart = 'January 1, 2007'

set @dtFiscalYearEnd = dateadd(yyyy, 1, @dtFiscalYEarStart)

set @iDaysInFiscalYear = datediff(d, @DtFiscalYearStart, @dtFiscalYearEnd)

declare @Numbers table(Num int, dtTemp smalldatetime)

insert into @Numbers select 0, @dtFiscalYEarStart

declare @i tinyint

set @i = 0

while @i < 9

begin

insert into @Numbers select Num + power(2,@i)

, Dateadd(d, power(2,@i), dtTemp) from @Numbers

set @i = @i + 1

end

delete from @Numbers where dtTemp >= @dtFiscalYearEnd

select dtTemp

, Num + 1 as FiscalDay

, Dense_Rank() over (Partition by Num % (7) order by dtTemp) as FiscalWeek

, case

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 1 and 4 then 1

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 5 and 8 then 2

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 9 and 13 then 3

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 14 and 17 then 4

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 18 and 21 then 5

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 22 and 26 then 6

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 27 and 30 then 7

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 31 and 34 then 8

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 35 and 39 then 9

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 40 and 43 then 10

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 44 and 47 then 11

else 12

end

as [FiscalMonth]

, case

when datediff(d, @dtFiscalYEarStart, dtTEmp) < (91*1) then 1

when datediff(d, @dtFiscalYEarStart, dtTEmp) < (91*2) then 2

when datediff(d, @dtFiscalYEarStart, dtTEmp) < (91*3) then 3

else 4

end

as FiscalQuarter

, datepart(dy,dtTemp) as CalendarDayOfYear

, Datepart(wk,dtTemp) as CalendarWeekOfYear

, Datepart(m,dtTemp) as CalendarMonth

, Datepart(q,dtTemp) as CalendarQuarter

from @Numbers

order by Dateadd(d, Num, @dtFiscalYearStart)

• Proposed as answer by Naomi NModerator Thursday, December 09, 2010 3:00 AM

Oh rusag2! I Bow To Thee!!!

I truly apologize for not looking at your post for this long. I have not figured out how to get alerts in my emal when a post is entered.

Your code is really amazing. I ran it and am now comparing it with a physical copy of a fiscal calendar I got from Finance. We seem to be off by a day. You see, at DBL we end our weeks on a Sat; the first month therefore ends on the 27th instead of the 28th, and although the 28th happens to be a Sunday it is a big deal for us as we process e-commerce orders on Sundays. Further, our year will end on the 29th and 2008 will being on the 30th.

As I would not know where to being modifying your code to accomplish the above I would sincerely appreciate some direction from you.

Thanks so much for taking the time to write the code!!! I would love to use it but need to make the changes I have indicated above.

Thanks again!

Near the very top, there is a "FiscalYearStart" variable. Currently, it's set to January 1, 2007. That's a Monday...which, following a 4, 4, 5 rule, (which is weeks) then if the fiscal year starts on January 1, 2007...well then the week ends on Sunday.

Try changing the value of that variable to "December 31, 2006" (that's a sunday). That way, the last day of the week will be saturday.

Thanks!

I almost get what I want when I start the date on December 31, 2006. I need to check with Finance if it is correct though.

The other issue is that December 2007 should end on the 29th, and fiscal 2008 should start on December 30th. How can I get your code to do this.

Early on you mentioned that the last quarter needed to be 93 days; can we not have that be the case because at DBL we actually end our fiscal year on the 29th.

Ok, the first answer was a bit...over the top.

Try this. Explicitly define the StartOfFiscalYear and EndOfFiscalYear dates:

Code Snippet

--A few variables:

declare @dtFiscalYearStart smalldatetime

, @dtFiscalYearEnd smalldatetime

, @iTemp int

---This is the table we'll populate and return at the end

declare @tb table(DayOfFiscalYear int identity (1,1)

,CalendarDate smalldatetime

, FiscalWeek int

, FiscalMonth tinyint

, FiscalQuarter tinyint)

--Now, populate our variables:

--This can be any date you choose. We assume that the fiscal year

--begins on the first day of the "fiscal week"

--We explicity populated STart of Fiscal Year and End

set @dtFiscalYearStart = 'December 31, 2006'

set @dtFiscalYearEnd = 'December 29, 2007'

set @iTemp = 0

--Here's the loop to populate our output table:

while not exists(select * from @tb where CalendarDate >= @dtFiscalYearEnd)

begin

insert into @tb (CalendarDate, FiscalWeek)

select dateadd(dd, @iTemp, @dtFiscalYearStart), (@iTemp / 7) + 1

set @iTEmp = @iTemp + 1

end

update @tb set FiscalMonth = 1, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 5

update @tb set FiscalMonth = 2, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 9

update @tb set FiscalMonth = 3, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 14

update @tb set FiscalMonth = 4, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 18

update @tb set FiscalMonth = 5, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 22

update @tb set FiscalMonth = 6, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 27

update @tb set FiscalMonth = 7, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 31

update @tb set FiscalMonth = 8, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 35

update @tb set FiscalMonth = 9, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 40

update @tb set FiscalMonth = 10, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 44

update @tb set FiscalMonth = 11, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 48

update @tb set FiscalMonth = 12, FiscalQuarter = 4 where fiscalweek > 47

--Be sure you recognize that going 4-4-5, 4-4-5, 4-4-5, 4-4-5 does not a whole year.

--you're still a couple days short. In calendar year 2007, there are three days in the 53rd week!

--uncomment this for a double-check of week counts

--select FiscalMonth,count(distinct fiscalWeek) from @tb group by FiscalMonth

select * from @tb

Well rusag2, life is just about the get more interesting.

I just had a conversation with Finance and they confirmed that the Fiscal Calendar is not going to have 365 days all the time. Further, the calendar method that has been in use for awhile is called the Retail Calendar, which is what I need to take a look at.

Basically, every so often the last quarter of the year becomes a 4-4-6 to "catch-up" for a wekk lost in previous years.

I have to get to the bottom of this and so am going to do some research on how the Retail Calendar can be programmed. Apparently all the retail stores have this programmed so I am hoping there is something out there.

I will keep you posted.

Thanks a bunch again for your efforts!!

desibull

rusag2:

Can you modify your code to accept the start dt, end dt, and the number of weeks for the last quarter as variables and then just cutoff the year when you reach that last day?

Let me know if I am pushing my luck! Your code is almost there and I really would like it to work.

Use my most recently posted code. You explicity specify the Fiscal Year Start Date and Fiscal Year End Date.

Then, I build the year, one week at a time, going 4-4-5 for each quarter until the last when I go 4-4-<Whatever Is Left>.

I did and it is almost working like a charm exceptthatfor some reason the script iscreatingan extraday at the end. Any clues why?

For example, I provided the following values: Start Dt: December 31, 2006 End Dt: December 29, 2007. I got a record for 12/30/07, which I should not. It should bepart of 08.

This is a quesion of "Through" vs. "To"

Just adjust your end date.

Or you may change this:

CalendarDate >= @dtFiscalYearEnd

to this:

CalendarDate > @dtFiscalYearEnd

Actually, setting it to >= worked. Somehow it got changed to >.

Your code now is fully functional.

I cannot thank you enough, rusag2. When I have some spare time I would like to go through your code and understand what you have done. Many of the functions you have used are new to me. It is one slick code though!!

Thanks!

Spoke too soon! Your second code while it produced the correct end date does not set the Fiscal month correctly. Your first code is working correctly. I made a similar change to the first one and it works.

Regardless, you are a genius!!

rusag2,
Would it be possible to change your code to create a 4-4-5 calendar table? If so how could this be done?

Thanks
BSOkc

I created a script that used roug2 as a base, but adds more detailed columns and also spans multiple years. Every 5 years, the last quarter changes to 455.

 

declare @dtFiscalYearStart smalldatetime
, @iTemp int
, @firstLeapYearInPeriod int
, @leapWeek int
, @currentDate datetime
, @fiscalDay int
, @fiscalWeek int
, @fiscalMonth int
, @fiscalQuarter int
, @fiscalYear int
, @leapYear int
, @lastYear int
, @firstDayOfYear datetime
, @firstDayOfQuarter datetime
, @firstDayOfMonth datetime
, @lastDayOfYear datetime
, @lastDayOfQuarter datetime
, @lastDayOfMonth datetime


/**************************************************
The following section needs to be populated for defining the 445 calendar
Starting here
***************************************************/

set @dtFiscalYearStart = 'December 26, 2010'
set @fiscalYear = 2011
set @lastYear = 2040
set @firstLeapYearInPeriod = 2011

/**************************************************
Ending here
***************************************************/

-- Holds the years that have 455 in last quarter
declare @leapTable table (leapyear int)

--Table to contain the fiscal year calendar
declare @tb table(
 periodDate datetime
, fiscalDay int
, fiscalWeek int
, fiscalMonth int
, fiscalQuarter int
, fiscalYear int
, firstDayOfMonth datetime null
, lastDayOfMonth datetime null
, firstDayOfQuarter datetime null
, lastDayOfQuarter datetime null
, firstDayOfYear datetime null
, lastDayOfYear datetime null)

--Set leap years in interval
set @leapYear = @firstLeapYearInPeriod
while (@leapYear < @lastYear)
begin
	insert into @leapTable values (@leapYear)
	set @leapYear = @leapYear + 5
end


set @fiscalDay = 1
set @fiscalWeek = 1
set @fiscalMonth = 1
set @fiscalQuarter = 1

-- Init params before loop
SET @currentDate = @dtFiscalYearStart

if ( exists (select * from @leapTable where @fiscalYear = leapyear))
begin
	set @leapWeek = 1
end
else
begin
	set @leapWeek = 0
end

set @fiscalWeek = 1




-- Loop on days in interval
while (datepart(yy,@currentDate) <= @lastYear)
begin

	
-- Set fiscal Month
	SELECT @fiscalMonth = CASE 
		WHEN @fiscalWeek BETWEEN 1 AND 4 THEN 1 --4
		WHEN @fiscalWeek BETWEEN 5 AND 8 THEN 2 --4
		WHEN @fiscalWeek BETWEEN 9 AND 13 THEN 3 --5
		WHEN @fiscalWeek BETWEEN 14 AND 17 THEN 4 --4
		WHEN @fiscalWeek BETWEEN 18 AND 21 THEN 5 --4
		WHEN @fiscalWeek BETWEEN 22 AND 26 THEN 6 --5
		WHEN @fiscalWeek BETWEEN 27 AND 30 THEN 7 --4
		WHEN @fiscalWeek BETWEEN 31 AND 34 THEN 8 --4
		WHEN @fiscalWeek BETWEEN 35 AND 39 THEN 9 --5
		WHEN @fiscalWeek BETWEEN 40 AND 43 THEN 10 --4
		WHEN @fiscalWeek BETWEEN 44 AND (47+@leapWeek) THEN 11 --4 or 5
		WHEN @fiscalWeek BETWEEN (48+@leapWeek) AND (52+@leapWeek) THEN 12 --5
	END

-- Set fiscal Quarter
	SELECT @fiscalQuarter = CASE 
		WHEN @fiscalMonth BETWEEN 1 AND 3 THEN 1
		WHEN @fiscalMonth BETWEEN 4 AND 6 THEN 2
		WHEN @fiscalMonth BETWEEN 7 AND 9 THEN 3
		WHEN @fiscalMonth BETWEEN 10 AND 12 THEN 4
	END

	insert into @tb (periodDate, fiscalDay, fiscalWeek, fiscalMonth, fiscalQuarter, fiscalYear) values 
	(@currentDate, @fiscalDay, @fiscalWeek, @fiscalMonth, @fiscalQuarter, @fiscalYear)

-- Set next day
	set @currentDate = dateadd(dd, 1, @currentDate)
	set @fiscalDay = @fiscalDay + 1
	set @fiscalWeek = ((@fiscalDay-1) / 7) + 1


	if (@fiscalWeek > (52+@leapWeek))
	begin
-- Reset a new year
		set @fiscalDay = 1
		set @fiscalWeek = 1
		set @fiscalYear = @fiscalYear + 1
		if ( exists (select * from @leapTable where @fiscalYear = leapyear))
		begin
			set @leapWeek = 1
		end
		else
		begin
			set @leapWeek = 0
		end
	end

end

update @tb
set firstDayOfMonth = minmax.startdate,
lastDayOfMonth = minmax.enddate
from
@tb t,
(
select fiscalMonth, fiscalQuarter, fiscalYear, min(perioddate) as startdate, max(perioddate) as enddate
from @tb
group by fiscalMonth, fiscalQuarter, fiscalYear
) minmax
where
t.fiscalMonth = minmax.fiscalMonth and
t.fiscalQuarter = minmax.fiscalQuarter and
t.fiscalYear = minmax.fiscalYear 


update @tb
set firstDayOfQuarter = minmax.startdate,
lastDayOfQuarter = minmax.enddate
from
@tb t,
(
	select fiscalQuarter, fiscalYear, min(perioddate) as startdate, max(perioddate) as enddate
	from @tb
	group by fiscalQuarter, fiscalYear
) minmax
where
t.fiscalQuarter = minmax.fiscalQuarter and
t.fiscalYear = minmax.fiscalYear 

update @tb
set firstDayOfYear = minmax.startdate,
lastDayOfYear = minmax.enddate
from
@tb t,
(
	select fiscalYear, min(perioddate) as startdate, max(perioddate) as enddate
	from @tb
	group by fiscalYear
) minmax
where
t.fiscalYear = minmax.fiscalYear 

select * from @tb
order by periodDate

I am a little confused people doesn't post set-based solutions, so I will.

DECLARE	@Year SMALLINT = 2007

;WITH cteSource(theDate, theFirst, theLast, ID)
AS (
	SELECT	DATEADD(YEAR, @Year - 1900, 7 * Number) AS theDate,
		DATEADD(YEAR, @Year - 1900, 0) AS theFirst,
		DATEADD(YEAR, @Year - 1899, -1) AS theLast,
		ROW_NUMBER() OVER (ORDER BY Number) - 1 AS ID
	FROM	master..spt_values
	WHERE	Type = 'P'
		AND Number BETWEEN 0 AND 52
), cteCalendar(theStart, theEnd, the13, the445, theFirst, theLast)
AS (
	SELECT	DATEADD(DAY, (DATEDIFF(DAY, -1, theDate) / 7) * 7, -1) AS theStart,
		DATEADD(DAY, (DATEDIFF(DAY, -1, theDate) / 7) * 7, 5) AS theEnd,
		CASE ID / 13
			WHEN 4 THEN 3
			ELSE ID / 13
		END AS the13,
		CASE 
			WHEN ID = 52 THEN 2
			WHEN (ID % 13) / 4 = 3 THEN 2
			ELSE (ID % 13) / 4
		END AS the445,
		theFirst,
		theLast
	FROM	cteSource
), cteWeeks(WeekStart, WeekEnd, the13, the445)
AS (
	SELECT	CASE
			WHEN theStart < theFirst THEN theFirst
			ELSE theStart
		END AS WeekStart,
		CASE
			WHEN theEnd > theLast THEN theLast
			ELSE theEnd
		END AS WeekEnd,
		the13,
		the445
	FROM	cteCalendar
)
SELECT		ROW_NUMBER() OVER (ORDER BY the13) AS WeekRange,
		MIN(WeekStart) AS WeekStart,
		MAX(WeekEnd) AS WeekEnd,
		CASE COUNT(*)
			WHEN 6 THEN 5
			ELSE COUNT(*)
		END AS WeekCount
FROM		cteWeeks
GROUP BY	the13,
		the445
ORDER BY	the13,
		the445

• Proposed as answer by Naomi NModerator Thursday, December 09, 2010 3:01 AM
• Marked as answer by Kalman TothModerator Wednesday, December 22, 2010 9:42 AM
• Unmarked as answer by Kalman TothModerator Wednesday, May 09, 2012 2:31 PM

Here is another option for you, rowset too...

 

DECLARE

     @fpYear smallint = 2010

    ,@fpMode tinyint = 4 -- 1 - 12 Calendar Months, 2 - 13 Four Week Periods, 3 - 454, 4 - 445, 5 - 544

    ,@fpStartDate date = NULL

    ,@fpDescPrefix nvarchar(25) = 'Period '

 

SET @fpStartDate = '20100101'

 

DECLARE @pcnt tinyint = CASE @fpMode WHEN 2 THEN 13 ELSE 12 END + 1;

 

WITH p AS

(

    SELECT

         1 as Number

        ,@fpStartDate as fpStartDate

        ,0 as md

        ,CASE WHEN @fpMode = 5 THEN 5 ELSE 4 END as ad

       

    UNION ALL

   

    SELECT

         p.Number + 1 as Number

        ,CASE

            WHEN @fpMode = 1 THEN DATEADD(MM, 1, p.fpStartDate)

            ELSE DATEADD(DD, p.ad * 7, p.fpStartDate) 

         END as fpStartDate

        ,p.Number % 3 as md

        ,CASE

            WHEN @fpMode = 3 AND p.md = 0

              OR @fpMode = 4 AND p.md = 1

              OR @fpMode = 5 AND p.md = 2

            THEN 5

            ELSE 4

         END as ad

    FROM p

    WHERE p.Number < @pcnt

), res AS

(

    SELECT

        p.*

        ,DATEADD(DD, - 1, np.fpStartDate) as fpEndDate

        ,DENSE_RANK() OVER(ORDER BY p.Number - p.md) as qtr

    FROM p

    JOIN p np ON np.Number = p.Number + 1

)

SELECT

     @fpYear as fpYear

    ,r.Number as fpPeriod

    ,@fpDescPrefix + RIGHT(REPLACE(STR(r.Number), ' ', '0'), 2) as fpDescription

    ,r.fpStartDate

    ,r.fpEndDate

    ,CASE WHEN r.qtr = 5 THEN 4 ELSE r.qtr END as fpQuarter

    ,DATEDIFF(ww,r.fpStartDate, r.fpEndDate) as diff

FROM res r  

 

 

Thank you to all contributors.

Can anyone please post a script to populate the same date for SSAS time table with all necessary fields?

[Date]

 

,[Year]

 

,[YearName]

 

,[HalfYear]

 

,[HalfYearName]

 

,[Quarter]

 

,[QuarterName]

 

,[Month]

 

,[MonthName]

 

,[Week]

 

,[WeekName]

 

,[DayOfYear]

 

,[DayOfYearName]

 

,[ReportingYear]

 

,[ReportingYearName]

 

,[ReportingHalfYear]

 

,[ReportingHalfYearName]

 

,[ReportingQuarter]

 

,[ReportingQuarterName]

 

,[ReportingMonth]

 

,[ReportingMonthName]

 

,[ReportingWeek]

 

,[ReportingWeekName]

 

,[ReportingDay]

 

,[ReportingDayName]

 

,[ReportingDayOfYear]

 

,[ReportingDayOfYearName]

 

,[ReportingDayOfHalfYear]

 

,[ReportingDayOfHalfYearName]

 

,[ReportingDayOfQuarter]

 

,[ReportingDayOfQuarterName]

 

,[ReportingDayOfMonth]

 

,[ReportingDayOfMonthName]

 

,[ReportingDayOfWeek]

 

,[ReportingDayOfWeekName]

 

,[ReportingWeekOfYear]

 

,[ReportingWeekOfYearName]

 

,[ReportingWeekOfHalfYear]

 

,[ReportingWeekOfHalfYearName]

 

,[ReportingWeekOfQuarter]

 

,[ReportingWeekOfQuarterName]

 

,[ReportingWeekOfMonth]

 

,[ReportingWeekOfMonthName]

 

,[ReportingMonthOfYear]

 

,[ReportingMonthOfYearName]

 

,[ReportingMonthOfHalfYear]

 

,[ReportingMonthOfHalfYearName]

 

,[ReportingMonthOfQuarter]

 

,[ReportingMonthOfQuarterName]

 

,[ReportingQuarterOfYear]

 

,[ReportingQuarterOfYearName]

 

,[ReportingQuarterOfHalfYear]

 

,[ReportingQuarterOfHalfYearName]

 

,[ReportingHalfYearOfYear]

 

,[ReportingHalfYearOfYearName]

 

 

Thanks,

Ayyappan

Wait a minute! You could have walked over Finance and gotten the correct fiscal calendar? You could assure that IT and Finance agree with an email?

Why did you want to use procedural code to generate it? A database mindset thinks in terms of data first .

Ever hear the old joke about the guy who locked his keys in his car? His wife sat in the passenger seat and yelled at him until he called AAA.

Wrong!!! Your code is kipping March and June.

Hi there,

Your script was very useful to me. The company I work for is moving to 544 Accounting starting August 29, 2011.

I modified your code to suit my needs. However, I need to populate the table till year 2025.

Currently, the script populates data only for a couple of years.

Could you please let me know how I can modify the script to populate data till 2025? Would appreciate your help on this.  Thank you.

-- Code for 544 Fiscal Year

Delete from rscperiod where timetype = 'Fiscal'

--insert into RSCPeriod(TimeType) select 'Fiscal' from Period
go
declare
      @dtFiscalYearStart smalldatetime
    ,@dtFiscalYearEnd smalldatetime
    ,@iDaysInFiscalYear int

     set @dtFiscalYearStart = 'Aug 29 2011'
     set @dtFiscalYearEnd =  dateadd(yyyy, 6, @dtFiscalYEarStart)
            

     /*(select max(thedate) from Period) 2025 in this Period Table; I need to assign this statement instead; But table not populating dates till 2025; It populates only till 2013*/ 

     set @iDaysInFiscalYear = datediff(d, @DtFiscalYearStart, @dtFiscalYearEnd)

declare @Numbers table(Num int, dtTemp datetime)

insert into @Numbers select 1, @dtFiscalYearStart

declare
            @i int
        set @i = 0
     
       while @i < 9
        begin
              insert into @Numbers
                     select Num + power(2,@i), Dateadd(d, power(2,@i), dtTemp)
              from @Numbers

              set @i = @i + 1
        end
select * from @Numbers
delete from @Numbers where dtTemp >= @dtFiscalYearEnd
 
Insert into RSCPeriod (TimeType,
                       TheDate,
                       DateNumber,
                        DateName,
                        YearNumber,
                        YearName,
                        QuarterNumber,
                        QuarterName,
                        MonthNumber,
                        MonthName1,
                        MonthName2)
      select
            'Fiscal',
            dtTemp,
            convert(int,CONVERT(VARCHAR(8), dtTemp, 112)),
            convert(VARCHAR(10), dtTemp, 105),
            datepart(yy,dtTemp),
           (
           case
           when (( Datepart(month,dtTemp) > 8 ) or  ((Datepart(month,dtTemp) = 8)
                 and (DATEPART(WEEK,DAY(dtTemp)))=5)) then
      
                     'Sep-' + cast(right(datepart(yy,dtTemp),2) as varchar(30)) + ' to ' + 'Aug-' + right(datepart(yy,dtTemp)+1,2)
           else                                               
                'Sep-' + cast(right(datepart(yy,dtTemp)-1,2) as varchar(30)) + ' to ' + 'Aug-' + right(datepart(yy,dtTemp),2)
           end
          ),
          (case
                             when datediff(d, @dtFiscalYearStart, dtTEmp) < (91*1) then 1
                             when datediff(d, @dtFiscalYearStart, dtTEmp) < (91*2) then 2
                             when datediff(d, @dtFiscalYearStart, dtTEmp) < (91*3) then 3
                  else 4
        end),

       (case
                             when datediff(d, @dtFiscalYearStart, dtTEmp) < (91*1) then 'Q1'
                             when datediff(d, @dtFiscalYearStart, dtTEmp) < (91*2) then 'Q2'
                             when datediff(d, @dtFiscalYearStart, dtTEmp) < (91*3) then 'Q3'
                         else 'Q4'
                end),

        Datepart(m,dtTemp),
        Datename(month,dtTemp),
       convert(varchar(3),Datename(month,dtTemp)) + '-' + convert(varchar(4),datepart(YY,dtTemp))
from @Numbers

 

Hi,

Can you please create a 4-5-4 calander for my company?

Regards,

Shiji

Hi SwePeso,

This script is really useful, thank you. However, I may have found a slight discrepancy in it. If I run it for 2000 I seem to "lose" the week over the February/March border. Check this out:

DECLARE	@Year SMALLINT = 2000

;WITH cteSource(theDate, theFirst, theLast, ID)
AS (
	SELECT	DATEADD(YEAR, @Year - 1900, 7 * Number) AS theDate,
		DATEADD(YEAR, @Year - 1900, 0) AS theFirst,
		DATEADD(YEAR, @Year - 1899, -1) AS theLast,
		ROW_NUMBER() OVER (ORDER BY Number) - 1 AS ID
	FROM	master..spt_values
	WHERE	Type = 'P'
		AND Number BETWEEN 0 AND 52
), cteCalendar(theStart, theEnd, the13, the445, theFirst, theLast)
AS (
	SELECT	DATEADD(DAY, (DATEDIFF(DAY, -1, theDate) / 7) * 7, -1) AS theStart,
		DATEADD(DAY, (DATEDIFF(DAY, -1, theDate) / 7) * 7, 5) AS theEnd,
		CASE ID / 13
			WHEN 4 THEN 3
			ELSE ID / 13
		END AS the13,
		CASE 
			WHEN ID = 52 THEN 2
			WHEN (ID % 13) / 4 = 3 THEN 2
			ELSE (ID % 13) / 4
		END AS the445,
		theFirst,
		theLast
	FROM	cteSource
), cteWeeks(WeekStart, WeekEnd, the13, the445)
AS (
	SELECT	CASE
			WHEN theStart < theFirst THEN theFirst
			ELSE theStart
		END AS WeekStart,
		CASE
			WHEN theEnd > theLast THEN theLast
			ELSE theEnd
		END AS WeekEnd,
		the13,
		the445
	FROM	cteCalendar
)
SELECT		ROW_NUMBER() OVER (ORDER BY the13) AS WeekRange,
		MIN(WeekStart) AS WeekStart,
		MAX(WeekEnd) AS WeekEnd,
		CASE COUNT(*)
			WHEN 6 THEN 5
			ELSE COUNT(*)
		END AS WeekCount,
		DATEDIFF(dd,MIN(WeekStart),MAX(WeekEnd))+1 AS DayTally
FROM		cteWeeks
GROUP BY	the13,
		the445
ORDER BY	the13,
		the445

Which returns:

Note the month that lasts for 42 days (i.e. 6 weeks). Now it may be that this is quite correct however select from [cteWeeks]:

DECLARE	@Year SMALLINT = 2000

;WITH cteSource(theDate, theFirst, theLast, ID)
AS (
	SELECT	DATEADD(YEAR, @Year - 1900, 7 * Number) AS theDate,
		DATEADD(YEAR, @Year - 1900, 0) AS theFirst,
		DATEADD(YEAR, @Year - 1899, -1) AS theLast,
		ROW_NUMBER() OVER (ORDER BY Number) - 1 AS ID
	FROM	master..spt_values
	WHERE	Type = 'P'
		AND Number BETWEEN 0 AND 52
), cteCalendar(theStart, theEnd, the13, the445, theFirst, theLast)
AS (
	SELECT	DATEADD(DAY, (DATEDIFF(DAY, -1, theDate) / 7) * 7, -1) AS theStart,
		DATEADD(DAY, (DATEDIFF(DAY, -1, theDate) / 7) * 7, 5) AS theEnd,
		CASE ID / 13
			WHEN 4 THEN 3
			ELSE ID / 13
		END AS the13,
		CASE 
			WHEN ID = 52 THEN 2
			WHEN (ID % 13) / 4 = 3 THEN 2
			ELSE (ID % 13) / 4
		END AS the445,
		theFirst,
		theLast
	FROM	cteSource
), cteWeeks(WeekStart, WeekEnd, the13, the445)
AS (
	SELECT	CASE
			WHEN theStart < theFirst THEN theFirst
			ELSE theStart
		END AS WeekStart,
		CASE
			WHEN theEnd > theLast THEN theLast
			ELSE theEnd
		END AS WeekEnd,
		the13,
		the445
	FROM	cteCalendar
)
select * from cteWeeks
--SELECT		ROW_NUMBER() OVER (ORDER BY the13) AS WeekRange,
--		MIN(WeekStart) AS WeekStart,
--		MAX(WeekEnd) AS WeekEnd,
--		CASE COUNT(*)
--			WHEN 6 THEN 5
--			ELSE COUNT(*)
--		END AS WeekCount,
--		DATEDIFF(dd,MIN(WeekStart),MAX(WeekEnd))+1 AS DayTally
--FROM		cteWeeks
--GROUP BY	the13,
--		the445
--ORDER BY	the13,
--		the445

to get this:

Notice how the week 2000-02-27 to 2000-03-04 seems to have disappeared.

Its difficult to corroborate whether this is correct or not however it does seem slightly dubious. It is affecting me directly because I am trying to INNER JOIN the output from [cteWeeks] with a list of contiguous dates using:

JOIN [cteWeeks] w					ON		d.[Dte] BETWEEN w.WeekStart AND w.[WeekEnd]

this causes me to lose those 7 days from my contiguous list of dates.

Just wondering  what your take on this might be before I dig in and try and fix for my scenario.

Regards

JT

SwePeso,

OK, I think I've figured it out. My suspicions arose when I realised that the missing week is over 2000-02-29. This:

DECLARE	@Year SMALLINT = 2000

;WITH cteSource--(theDate, theFirst, theLast, ID)
AS (
	SELECT	Number,
	DATEADD(YEAR, @Year - 1900, 7 * Number) AS theDate,
		DATEADD(YEAR, @Year - 1900, 0) AS theFirst,
		DATEADD(YEAR, @Year - 1899, -1) AS theLast,
		ROW_NUMBER() OVER (ORDER BY Number) - 1 AS ID
	FROM	master..spt_values
	WHERE	Type = 'P'
		AND Number BETWEEN 0 AND 52
)
select * from cteSource

returns '2000-02-26' & '2000-03-05' as week start dates which means its calculated there to be an 8 day week (02/26, 02/27, 02/28, 02/29, 03/01, 03/02, 03/03, 03/04).

The reason is that the logic is based on number of years since a given day in the year 1900 and apparently (according to SQL Server anyhow) 1900 is NOT considered to be a leap year. To prove it run this:

select cast(58 as smalldatetime),cast(59 as smalldatetime)

An interesting little conundrum if nothing else.

There is nothing I can do. If you look at cteSource only, you can see that SQL Server skips the leap day of February 29.
It results in that the day 7 days after Feburary 26 is March 5, not the correct March 4.

This is a bug in SQL Server.

//Peter

Looks like we came to the same conclusion at the same time :)

Apparently its not a bug mind you. 

http://www.timeanddate.com/date/leapyear.html

Damn Pope Gregory and his inconsistent date system :)

Fixed. I just added another abstraction for the year limits first. The bug in my code was that I added n number of days to 1900 and then added 100 years to display 2000, which was wrong. 1900 is not a leap year, that's why the bug was present in my previous code.

Here is a correct one.

DECLARE	@Year SMALLINT = 2000

;WITH cteLimits(theFirst, theLast)
AS (
	SELECT	DATEADD(YEAR, @Year - 1900, '19000101') AS theFirst,
		DATEADD(YEAR, @Year - 1899, '18991231') AS theLast
), cteSource(theDate, theFirst, theLast, ID)
AS (
	SELECT		DATEADD(DAY, 7 * v.Number, l.theFirst) AS theDate,
			l.theFirst,
			l.theLast,
			v.Number AS ID
	FROM		cteLimits AS l
	INNER JOIN	master.dbo.spt_values AS v ON v.Type = 'P'
				AND v.Number BETWEEN 0 AND 52
), cteCalendar(theStart, theEnd, the13, the445, theFirst, theLast)
AS (
	SELECT	DATEADD(DAY, DATEDIFF(DAY, '18991231', theDate) / 7 * 7, '18991231') AS theStart,
		DATEADD(DAY, DATEDIFF(DAY, '18991231', theDate) / 7 * 7, '19000106') AS theEnd,
		CASE ID / 13
			WHEN 4 THEN 3
			ELSE ID / 13
		END AS the13,
		CASE 
			WHEN ID = 52 THEN 2
			WHEN (ID % 13) / 4 = 3 THEN 2
			ELSE (ID % 13) / 4
		END AS the445,
		theFirst,
		theLast
	FROM	cteSource
), cteWeeks(WeekStart, WeekEnd, the13, the445)
AS (
	SELECT	CASE
			WHEN theStart < theFirst THEN theFirst
			ELSE theStart
		END AS WeekStart,
		CASE
			WHEN theEnd > theLast THEN theLast
			ELSE theEnd
		END AS WeekEnd,
		the13,
		the445
	FROM	cteCalendar
)
SELECT		ROW_NUMBER() OVER (ORDER BY the13) AS WeekRange,
		MIN(WeekStart) AS WeekStart,
		MAX(WeekEnd) AS WeekEnd,
		CASE COUNT(*)
			WHEN 6 THEN 5
			ELSE COUNT(*)
		END AS WeekCount
FROM		cteWeeks
GROUP BY	the13,
		the445
ORDER BY	the13,
		the445

Hi Peter, FYI, that loses 2000-12-31 (I think because its in the 54th week). Don't worry, I'm not asking you to fix it (I suspect you're a little exasperated by this now :) ). Just thought you'd wan

Well, the 2000-12-31 was included in the october "gang", due to 2000-12-31 being the 366th day, which is 2 more than 7 * 4 * (4+4+5) = 364

For the year of 2000, it means last "5" week period actually is 6 weeks and 1 day.

Here is the corrected code

DECLARE	@Year SMALLINT = 2007

;WITH cteLimits(theFirst, theLast)
AS (
	SELECT	DATEADD(YEAR, @Year - 1900, '19000101') AS theFirst,
		DATEADD(YEAR, @Year - 1900, '19001231') AS theLast
), cteSource(theDate, theFirst, theLast, ID)
AS (
	SELECT		DATEADD(DAY, 7 * v.Number, l.theFirst) AS theDate,
			l.theFirst,
			l.theLast,
			v.Number AS ID
	FROM		cteLimits AS l
	INNER JOIN	master.dbo.spt_values AS v ON v.Type = 'P'
				AND v.Number BETWEEN 0 AND 53
), cteCalendar(theStart, theEnd, the13, the445, theFirst, theLast)
AS (
	SELECT	DATEADD(DAY, DATEDIFF(DAY, '18991231', theDate) / 7 * 7, '18991231') AS theStart,
		DATEADD(DAY, DATEDIFF(DAY, '18991231', theDate) / 7 * 7, '19000106') AS theEnd,
		CASE ID / 13
			WHEN 4 THEN 3
			ELSE ID / 13
		END AS the13,
		CASE 
			WHEN ID >= 52 THEN 2
			WHEN (ID % 13) / 4 = 3 THEN 2
			ELSE (ID % 13) / 4
		END AS the445,
		theFirst,
		theLast
	FROM	cteSource
), cteWeeks(WeekStart, WeekEnd, the13, the445)
AS (
	SELECT	CASE
			WHEN theStart < theFirst THEN theFirst
			ELSE theStart
		END AS WeekStart,
		CASE
			WHEN theEnd > theLast THEN theLast
			ELSE theEnd
		END AS WeekEnd,
		the13,
		the445
	FROM	cteCalendar
)
SELECT		ROW_NUMBER() OVER (ORDER BY the13) AS WeekRange,
		MIN(WeekStart) AS WeekStart,
		MAX(WeekEnd) AS WeekEnd,
		CASE
			WHEN COUNT(*) >= 5 THEN 5
			ELSE COUNT(*)
		END AS WeekCount
FROM		cteWeeks
GROUP BY	the13,
		the445
ORDER BY	the13,
		the445

Sure.

DECLARE	@Year SMALLINT = 2007

;WITH cteLimits(theFirst, theLast)
AS (
	SELECT	DATEADD(YEAR, @Year - 1900, '19000101') AS theFirst,
		DATEADD(YEAR, @Year - 1900, '19001231') AS theLast
), cteSource(theDate, theFirst, theLast, ID)
AS (
	SELECT		DATEADD(DAY, 7 * v.Number, l.theFirst) AS theDate,
			l.theFirst,
			l.theLast,
			v.Number AS ID
	FROM		cteLimits AS l
	INNER JOIN	master.dbo.spt_values AS v ON v.Type = 'P'
				AND v.Number BETWEEN 0 AND 53
), cteCalendar(theStart, theEnd, the13, the454, theFirst, theLast)
AS (
	SELECT	DATEADD(DAY, DATEDIFF(DAY, '18991231', theDate) / 7 * 7, '18991231') AS theStart,
		DATEADD(DAY, DATEDIFF(DAY, '18991231', theDate) / 7 * 7, '19000106') AS theEnd,
		CASE ID / 13
			WHEN 4 THEN 3
			ELSE ID / 13
		END AS the13,
		CASE 
			WHEN ID >= 52 THEN 2
			WHEN ID % 13 BETWEEN 4 AND 8 THEN 1
			WHEN ID % 13 BETWEEN 0 AND 3 THEN 0
			ELSE 2
		END AS the454,
		theFirst,
		theLast
	FROM	cteSource
), cteWeeks(WeekStart, WeekEnd, the13, the454)
AS (
	SELECT	CASE
			WHEN theStart < theFirst THEN theFirst
			ELSE theStart
		END AS WeekStart,
		CASE
			WHEN theEnd > theLast THEN theLast
			ELSE theEnd
		END AS WeekEnd,
		the13,
		the454
	FROM	cteCalendar
)
SELECT		ROW_NUMBER() OVER (ORDER BY the13) AS WeekRange,
		MIN(WeekStart) AS WeekStart,
		MAX(WeekEnd) AS WeekEnd,
		CASE
			WHEN COUNT(*) >= 5 THEN 5
			ELSE COUNT(*)
		END AS WeekCount
FROM		cteWeeks
GROUP BY	the13,
		the454
ORDER BY	the13,
		the454

Hi Peter,

I really can't thank you enough for taking the time to amend your script. You had no obligation to do so but you did it anyway, I'm very grateful.

In case anyone is bothered I have adapted Peter's script so that it works for multiple years and joins it back to a list of dates to produce 4-4-5 information for all of those dates - in effect a date dimension table with 4-4-5 data. Here it is, although note that it only works on SQL Server 2012 or SQL Azure (because it uses the DATEFROMPARTS function) (the last three columns pertain to 4-4-5):

--SELECT * FROM ent.Dates
DECLARE	@StartYear SMALLINT = 2000,
		@EndYear SMALLINT = 2050;
WITH	[ctedates]([Cal_Dte]) AS (
		SELECT	DATEFROMPARTS(@StartYear,1,1)
		UNION ALL
		SELECT	DATEADD(dd,1,d.[Cal_Dte])
		FROM	[ctedates]d
		WHERE	d.[Cal_Dte] < DATEFROMPARTS(@EndYear,12,31)
)
,[cteYears]([Yr]) AS (
	SELECT	[Yr]	=	@StartYear
	UNION ALL
	SELECT	y.[Yr] + CONVERT(SMALLINT,1)
	FROM	[cteYears] y
	WHERE	y.[Yr] < @EndYear
)
,[cteLimits]([Yr],[theFirst], [theLast])
AS (
		SELECT	y.[Yr]
		,		[theFirst]	=	DATEADD(YEAR, y.[Yr] - 1900, '19000101')
		,		[theLast]	=	DATEADD(YEAR, y.[Yr] - 1899, '18991231')
		FROM	[cteYears] y
)
,[cteSource]([Yr],[theDate], [theFirst], [theLast], [ID])
AS (
	SELECT	l.Yr
	,		[theDate]	=	DATEADD(DAY, 7 * sv.[Number], l.[theFirst])
	,		[theFirst]	=	l.[theFirst]
	,		[theLast]	=	l.[theLast]
	,		[ID]		=	sv.[number]
	FROM	[master]..[spt_values] sv
	CROSS JOIN cteLimits l
	WHERE	sv.[type] = 'P'
		AND sv.[number] BETWEEN 0 AND 53
)
, [cteCalendar]([Yr],[theStart], [theEnd], [the13], [the445], [theFirst], [theLast])
AS (
	SELECT	[Yr]
	,		[theStart]	=	DATEADD(DAY, DATEDIFF(DAY, '18991231', theDate) / 7 * 7, '18991231')
	,		[theEnd]	=	DATEADD(DAY, DATEDIFF(DAY, '18991231', theDate) / 7 * 7, '19000106')
	,		[the13]		=	CASE [ID] / 13	WHEN 4 THEN 3
											ELSE [ID] / 13
							END
	,		[the445]	=	CASE	WHEN [ID] = 52 THEN 2
									WHEN ([ID] % 13) / 4 = 3 THEN 2
									ELSE ([ID] % 13) / 4
							END
	,		[theFirst]
	,		[theLast]
	FROM	[cteSource]
)
, [cteWeeks]([Yr],[WeekNumber],[WeekStart], [WeekEnd], [the13], [the445])
AS (
	SELECT	[Yr]
	,		[WeekNumber]	=	ROW_NUMBER() OVER (PARTITION BY [Yr] ORDER BY [theStart])
	,		[WeekStart]		=	CASE	WHEN [theStart] < [theFirst] THEN [theFirst]
										ELSE [theStart]
								END
	,		[WeekEnd]		=	CASE	WHEN [theEnd] > [theLast] THEN [theLast]
										ELSE [theEnd]
								END
	,		[the13]
	,		[the445]
	FROM	[cteCalendar]
)
SELECT	[Dte_EK]			=	(YEAR(d.[Cal_Dte]) * 10000) + (MONTH(d.[Cal_Dte]) * 100) + (DAY(d.[Cal_Dte]))
,		d.[Cal_Dte]
,		[CalYr_Nmbr]		=	YEAR(d.[Cal_Dte])
,		[CalMon_Nmbr]		=	MONTH(d.[Cal_Dte])
,		[CalDayOfYr_Nmbr]	=	DATEPART(dy,d.[Cal_Dte])
,		[CalDayOfMon_Nmbr]	=	DATEPART(dd,d.[Cal_Dte])
,		[CalDayOfWk_Nmbr]	=	DATEPART(dw,d.[Cal_Dte])
,		[CalYrMon_Dte_Nmbr]	=	(YEAR(d.[Cal_Dte]) * 100) + (MONTH(d.[Cal_Dte]))
,		[CalMon_Name]		=	DATENAME(mm,d.[Cal_Dte])
,		[CalDayOfWk_Name]	=	DATENAME(dw,d.[Cal_Dte])
,		[CallsMon_Ind]		=	CASE DATENAME(dw,d.[Cal_Dte]) WHEN 'Monday' THEN 1 ELSE 0 END
,		[CallsTue_Ind]		=	CASE DATENAME(dw,d.[Cal_Dte]) WHEN 'Tuesday' THEN 1 ELSE 0 END
,		[CallsWed_Ind]		=	CASE DATENAME(dw,d.[Cal_Dte]) WHEN 'Wednesday' THEN 1 ELSE 0 END
,		[CallsThrs_Ind]		=	CASE DATENAME(dw,d.[Cal_Dte]) WHEN 'Thursday' THEN 1 ELSE 0 END
,		[CallsFri_Ind]		=	CASE DATENAME(dw,d.[Cal_Dte]) WHEN 'Friday' THEN 1 ELSE 0 END
,		[CallsSat_Ind]		=	CASE DATENAME(dw,d.[Cal_Dte]) WHEN 'Saturday' THEN 1 ELSE 0 END
,		[CallsSun_Ind]		=	CASE DATENAME(dw,d.[Cal_Dte]) WHEN 'Sunday' THEN 1 ELSE 0 END
,		[CallsWkDay_Ind]	=	CASE WHEN DATENAME(dw,d.[Cal_Dte]) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END
,		[CallsWkend_Ind]	=	CASE WHEN DATENAME(dw,d.[Cal_Dte]) IN ('Saturday','Sunday') THEN 1 ELSE 0 END
,		[CalQtr_Nmbr]		=	DATEPART(q,d.[Cal_Dte])
,		[CalQtr_Name]		=	CONVERT(CHAR(4),YEAR(d.[Cal_Dte])) + 'CALQ0' + CONVERT(CHAR(1),DATEPART(q,d.[Cal_Dte]))
,		[CalWk_Nmbr]		=	DATEPART(WEEK,d.[Cal_Dte])
,		[CalWk_Name]		=	CONVERT(CHAR(4),YEAR(d.[Cal_Dte])) + 'CALW' + RIGHT('0' + CONVERT(VARCHAR(2),DATEPART(WEEK,d.[Cal_Dte])),2)
,		[CalQtr445_Nmbr]	=	w.[the13] + 1
,		[CalMon445_Nmbr]	=	w.the445 + 1 + (w.[the13] * 3)
,		[CalWk445_Nmbr]		=	w.WeekNumber
FROM	[ctedates] d
INNER JOIN [cteWeeks] w					ON		d.[Cal_Dte] BETWEEN w.[WeekStart] AND w.[WeekEnd]
OPTION (MAXRECURSION 0)

Thanks again Peter.

JT

I have found this so useful as i need to report on 445 but have a couple of questions....

1) my fiscal year runs from 1 Apr yyyy to 31 mar yyyy ( i can adapt this code to cover that)

2) is there a way of working out what the week of the month is where the week runs from Sat to Fri

this means that week 1 of apr could have less than 7 days in it, if the 1 apr is a weekday and the last week of the year could have more than 7 days in it..

my year MUST start on 1/4/yyyy and Week Must start on Saturday and need to show week number of the month (1,2,3,4,5) 

date	month of year	week of month
01 April 2013	1	1
02 April 2013	1	1
03 April 2013	1	1
04 April 2013	1	1
05 April 2013	1	1
06 April 2013	1	2
07 April 2013	1	2
08 April 2013	1	2
09 April 2013	1	2
10 April 2013	1	2
11 April 2013	1	2
12 April 2013	1	2
13 April 2013	1	3
14 April 2013	1	3
15 April 2013	1	3
16 April 2013	1	3
17 April 2013	1	3
18 April 2013	1	3
19 April 2013	1	3
20 April 2013	1	4
21 April 2013	1	4
22 April 2013	1	4
23 April 2013	1	4
24 April 2013	1	4
25 April 2013	1	4
26 April 2013	1	4
27 April 2013	2	1
28 April 2013	2	1
29 April 2013	2	1
30 April 2013	2	1
01 May 2013	2	1
02 May 2013	2	1
03 May 2013	2	1
04 May 2013	2	2
05 May 2013	2	2
06 May 2013	2	2

any help gratefully recieved

A much simpler version

DECLARE	@Year SMALLINT = 2000;

SELECT		d.WeekRange,
		CASE
			WHEN d.FromDayOfYear < x.theYearStart THEN x.theYearStart
			ELSE d.FromDayOfYear
		END AS WeekStart,
		CASE
			WHEN d.ToDayOfYear > x.theYearEnd THEN x.theYearEnd
			ELSE d.ToDayOfYear
		END AS WeekEnd,
		d.WeekCount
FROM		(
			VALUES	(DATEADD(YEAR, @Year - 1900, '19000101'), DATEADD(YEAR, @Year - 1899, '18991231'))
		) AS x(theYearStart, theYearEnd)
CROSS APPLY	(
			VALUES	(DATEADD(DAY, DATEDIFF(DAY, '18991231', x.theYearStart) / 7 * 7, '18991231'))
		) AS c(theFirstWeek)
CROSS APPLY	(
			VALUES	( 1, DATEADD(DAY,   0, c.theFirstWeek), DATEADD(DAY,  27, c.theFirstWeek), 4),
				( 2, DATEADD(DAY,  28, c.theFirstWeek), DATEADD(DAY,  55, c.theFirstWeek), 4),
				( 3, DATEADD(DAY,  56, c.theFirstWeek), DATEADD(DAY,  90, c.theFirstWeek), 5),
				( 4, DATEADD(DAY,  91, c.theFirstWeek), DATEADD(DAY, 118, c.theFirstWeek), 4),
				( 5, DATEADD(DAY, 119, c.theFirstWeek), DATEADD(DAY, 146, c.theFirstWeek), 4),
				( 6, DATEADD(DAY, 147, c.theFirstWeek), DATEADD(DAY, 181, c.theFirstWeek), 5),
				( 7, DATEADD(DAY, 182, c.theFirstWeek), DATEADD(DAY, 209, c.theFirstWeek), 4),
				( 8, DATEADD(DAY, 210, c.theFirstWeek), DATEADD(DAY, 237, c.theFirstWeek), 4),
				( 9, DATEADD(DAY, 238, c.theFirstWeek), DATEADD(DAY, 272, c.theFirstWeek), 5),
				(10, DATEADD(DAY, 273, c.theFirstWeek), DATEADD(DAY, 300, c.theFirstWeek), 4),
				(11, DATEADD(DAY, 301, c.theFirstWeek), DATEADD(DAY, 328, c.theFirstWeek), 4),
				(12, DATEADD(DAY, 329, c.theFirstWeek), DATEADD(DAY, 372, c.theFirstWeek), 5)
		) AS d(WeekRange, FromDayOfYear, ToDayOfYear, WeekCount);

This is exactly what I was trying to do JT. Thanks for pulling it all together.  I can definitely use this with my scripts, as I'm using the DATEPART function of 2012 and 2014 also.  Saves a lot of code.

Thanks for going to the trouble.