USE EXTRACT;
DECLARE @startdate datetime, @fy int, @fyname char(7), @days int
--
-- Set the parameters below to the required values
--
-- SET @startdate = '2007-09-30'
SET @startdate = (SELECT MAX(pkdatDate)+1 FROM tblFinancialCalendar)
-- Comment out one of the above two lines: 
-- use the first if you want to set a fixed start date and use the second if you want to append a new year to the end of the existing table
SET @fy = 9
SET @fyname = 'FY09/10'
SET @days = 371
-- @days must be set to 364 for a 52 week year, or set to 371 for a 53 week year 
-- ( but check that the additional week is in the correct period in the 
-- CalendarFeeder table first or run prcCalendarFeeder53Setup to reset it )
--
-- Load the new year into the main table from the feeder table
--
INSERT INTO tblFinancialCalendar
SELECT 
	@startdate-1+pkintSeedDay,							-- pkdatDate
	pkintSeedDay,									-- intDayOfFY
	CASE WHEN @days=364 THEN intSeedWeekOfPeriod ELSE intSeedWeekOfPeriod53 END,	-- intWeeekOfPeriod
	CASE WHEN @days=364 THEN intSeedPeriod ELSE intSeedPeriod53 END,			-- intPeriodOfFY
	CASE 
		WHEN intSeedPeriod>= 1 and intSeedPeriod<=3 THEN 1
		WHEN  intSeedPeriod>= 4 and intSeedPeriod<=6 THEN 2
		WHEN  intSeedPeriod>= 7 and intSeedPeriod<=9 THEN 3
		WHEN  intSeedPeriod>= 10 and intSeedPeriod<=12 THEN 4
	END,										-- intQuarterOfFY
	@fy,										-- intFYID
	FLOOR((pkintSeedDay-1)/7)+1,							-- intWeekOfFY
	NULL,										-- intDayOfWeek (set below)
	NULL,										-- intWeeksAgo (reset daily) 
	NULL,										-- intDaysAgo (reset daily) 
	NULL										-- intPeriodsAgo (reset daily)
FROM tblCalendarFeeder
WHERE pkintSeedDay<=@days;
--
-- Update the calandar to set the "day of the week" values
-- (These are used to prevent issues caused by the DATEPART(dw,xxx) function which gives variable outputs dependent on language 
--  and other settings that may be in effect at a per-user level)
--
SET DATEFIRST 7;                                                                                      -- Our financial weeks run from Sunday to Saturday
UPDATE tblFinancialCalendar SET intDayOfWeek=DATEPART(dw,pkdatDate);
--
-- Add or update new year into the Financial Year Names table
--
DELETE FROM tblCalendarFinancialYearNames WHERE pkintFYID=@fy;
INSERT INTO tblCalendarFinancialYearNames
SELECT
@fy,
@fyname;

 

UPDATE tblFinancialCalendar SET 
			intWeeksAgo=DATEDIFF(wk,pkdatDate,GETDATE());
UPDATE tblFinancialCalendar SET 
			intDaysAgo=DATEDIFF(dy,pkdatDate,GETDATE());
UPDATE tblFinancialCalendar SET 
			intPeriodsAgo=(SELECT COUNT(DISTINCT intFYID*100+intPeriodOfFY)-1 
							FROM tblFinancialCalendar b 
							WHERE pkdatDate BETWEEN tblFinancialCalendar.pkdatDate AND GETDATE())
WHERE tblFinancialCalendar.pkdatDate<=GETDATE();
UPDATE tblFinancialCalendar SET 
			intPeriodsAgo=(SELECT -COUNT(DISTINCT intFYID*100+intPeriodOfFY)+1 
							FROM tblFinancialCalendar b 
							WHERE pkdatDate BETWEEN GETDATE() AND tblFinancialCalendar.pkdatDate)
WHERE tblFinancialCalendar.pkdatDate>GETDATE();

DECLARE @period int,@cut int;
--
-- Set the following parameter before running
--
SET @period=11;
-- Enter the period which is to have the extra 5th week (should be one of 1,2,4,5,7,8,10 or 11)
SET @cut=(SELECT MAX(pkintSeedDay) FROM tblCalendarFeeder WHERE intSeedPeriod=@period);
CREATE TABLE tblCalendarFeeder2 
(pkintSeedDay INT NOT NULL PRIMARY KEY,intSeedPeriod INT NOT NULL,intSeedWeekOfPeriod INT NOT NULL);
UPDATE tblCalendarFeeder SET intSeedPeriod53=NULL,intSeedWeekOfPeriod53=NULL;
INSERT INTO tblCalendarFeeder2 SELECT pkintSeedDay,intSeedPeriod,intSeedWeekOfPeriod FROM tblCalendarFeeder; 
UPDATE tblCalendarFeeder SET 
	intSeedPeriod53=(select intSeedPeriod from tblCalendarFeeder2 b 
			where tblCalendarFeeder.pkintSeedDay=b.pkintSeedDay and tblCalendarFeeder.pkintSeedDay<=@cut),
	intSeedWeekOfPeriod53=(select intSeedWeekOfPeriod from tblCalendarFeeder2 b 
			where tblCalendarFeeder.pkintSeedDay=b.pkintSeedDay and tblCalendarFeeder.pkintSeedDay<=@cut)
WHERE tblCalendarFeeder.pkintSeedDay<=@cut;
UPDATE tblCalendarFeeder SET 
	intSeedPeriod53=(select intSeedPeriod from tblCalendarFeeder2 b 
			where tblCalendarFeeder.pkintSeedDay=b.pkintSeedDay+7 and tblCalendarFeeder.pkintSeedDay>@cut+7),
	intSeedWeekOfPeriod53=(select intSeedWeekOfPeriod from tblCalendarFeeder2 b 
			where tblCalendarFeeder.pkintSeedDay=b.pkintSeedDay+7 and tblCalendarFeeder.pkintSeedDay>@cut+7)
WHERE tblCalendarFeeder.pkintSeedDay>@cut+7;
UPDATE tblCalendarFeeder SET intSeedPeriod53=@period,intSeedWeekOfPeriod53=5 WHERE intSeedPeriod53 IS NULL;
DROP TABLE tblCalendarFeeder2;