use tempdb
go
CREATE PROCEDURE  ai_GenerateFiscalCalendar
/*
	Expands a financial year specification (start date and weeks-per month pattern)
	into a set of daily update statements for an existing Date Dimension table.

	Expands one financial year at a time.

	PRE-REQUISITES : 
		* Min SQL Server 2012 (for window function).

	OUTPUTS :
		* A summary of fiscal periods with counts is printed to standard output.
		* Update statements returned as rowset for updating @TableToUpdate.

	PARAMETERS :
	@FirstDayOfFiscalYear
		(date) The date of the first day of first period of fiscal year.
	@FiscalYearName
		(string) Identifier for the fiscal year (e.g. '2017'). This is only used in update statements (if required).

	@TableToUpdate
		The name of the table to be updated, including schema if necessary, as it would appear in an UPDATE statement.
	@DateKeyColumn
		The Date-Key column of table to update.  Column must be integer conversion of date format 112 (yyyymmdd).
	@ColumnUpdatePattern
		A string containing multiple update statements as they would appear following SET in an UPDATE statement.
		Inverted commas must be escaped. The following placeholders can be used.
		{year}						:  the value of @FiscalYearName
		{d}, {w}, {m}, {q}, {s}		:  day, week, month, quarter and semester numbers.
		{wst}, {mst}, {qst}, {sst}  :  week, month, quarter, semester start date key
		{wend}, {mend}, {qend}, {send} :  week, month, quarter, semester end date key 
	@WeeksPerMonthPattern
		A comma separated set of values indicating the number of weeks in each fiscal month.
		e.g. '4,4,5,4,4,5,4,4,5,4,4,5'.
	@MonthsPerQuarterPattern
		A comma separated set of values indicating the number of months in each fiscal quarter.
		e.g. '3,3,3,3'
	@NbrOfWeeks
		(int) The total number of weeks in the fiscal year.  Default is 52. Override this value
		for 53 week or non-standard fiscal years.


	EXAMPLE SUMMARY OUTPUT :

		FISCAL YEAR: 2017
		START DATE: 2017-04-30 Sunday
		TOTAL DAYS: 364
		TOTAL WEEKS: 52
		MONTHS:
		 1  2017-04-30 Sun  28 days  4 weeks  Qtr: 1  Sem: 1
		 2  2017-05-28 Sun  28 days  4 weeks  Qtr: 1  Sem: 1
		 3  2017-06-25 Sun  35 days  5 weeks  Qtr: 1  Sem: 1
		 4  2017-07-30 Sun  28 days  4 weeks  Qtr: 2  Sem: 1
		 5  2017-08-27 Sun  28 days  4 weeks  Qtr: 2  Sem: 1
		 6  2017-09-24 Sun  35 days  5 weeks  Qtr: 2  Sem: 1
		 7  2017-10-29 Sun  28 days  4 weeks  Qtr: 3  Sem: 2
		 8  2017-11-26 Sun  28 days  4 weeks  Qtr: 3  Sem: 2
		 9  2017-12-24 Sun  35 days  5 weeks  Qtr: 3  Sem: 2
		10  2018-01-28 Sun  28 days  4 weeks  Qtr: 4  Sem: 2
		11  2018-02-25 Sun  28 days  4 weeks  Qtr: 4  Sem: 2
		12  2018-03-25 Sun  35 days  5 weeks  Qtr: 4  Sem: 2


	HISTORY
	Andrew Ing. Ver 1.  22/4/2017.
*/

	@FirstDayOfFiscalYear date = '2017-04-30',
	@FiscalYearName varchar(50) = '2017',
	@TableToUpdate varchar(50) = 'DestinationTableName',
	@DateKeyColumn varchar(50) = 'DateKey',
	@ColumnUpdatePattern varchar(1000) = 'Wk={w}, Mth={m}, Qtr={q}, S={s}, WkStart = {wst}, WkEnd={wend}, MStart={mst}, Mend={mend}, QStart={qst}, QEnd={qend}, SStart={sst}, SEnd={send}',
	@WeeksPerMonthPattern varchar(100) = '4,4,5,4,4,5,4,4,5,4,4,5',
	@MonthsPerQuarterPattern varchar(100) = '3,3,3,3',
	@NbrOfWeeks int = 52

AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @WeeksPerMonth_XML xml;
DECLARE @MonthsPerQuarter_XML xml;
DECLARE @NbrOfDays  int;
DECLARE @x int;
DECLARE @MonthSummary varchar(1000);


BEGIN TRY
-- Validation.
	IF (REPLACE(@WeeksPerMonthPattern, ',', '') LIKE '%[^0-9]%') RAISERROR ('Illegal character in @WeeksPerMonthPatter. Numbers and commas only.', 16, 1); 
	IF (REPLACE(@MonthsPerQuarterPattern, ',', '') LIKE '%[^0-9]%') RAISERROR ('Illegal character in @MonthsPerQuarterPattern. Numbers and commas only.', 16,1);

	CREATE TABLE #ai_FiscalPeriods (DayNbr int IDENTITY (1,1), [Date] date, WeekNbr int, MonthNbr tinyint, QuarterNbr tinyint, SemesterNbr tinyint);
-- Convert csv patterns to xml.
	SET @WeeksPerMonth_XML = CAST(('<x>'+ REPLACE(@WeeksPerMonthPattern, ',' ,'</x><x>')+'</x>') AS xml);
	SET @MonthsPerQuarter_XML = CAST(('<x>' + REPLACE(@MonthsPerQuarterPattern, ',', '</x><x>') + '</x>') AS xml);

	IF ((SELECT SUM(N.value('.', 'smallint')) FROM @MonthsPerQuarter_XML.nodes('x') AS T(N)) 
		<> (SELECT COUNT(*) FROM @WeeksPerMonth_XML.nodes('x') AS T(N)) ) RAISERROR('The total months in @MonthsPerQuarterPattern doesn''t match number of months in @WeeksPerMonthPattern.', 16, 1);

	IF (@NbrOfWeeks <> (SELECT SUM(N.value('.', 'tinyint')) FROM @WeeksPerMonth_XML.nodes('x') as T(N)))
		RAISERROR('The total number of weeks specified in @WeeksPerMonthPattern does not match the @NbrOfWeeks parameter.', 16, 1);
-- Insert one row per day into table adn add week divisions.
	SET @x = 0;
	WHILE (@x < (@NbrOfWeeks * 7))
	BEGIN
		INSERT INTO #ai_FiscalPeriods ([Date], WeekNbr) 
			SELECT DATEADD(DAY, @x, @FirstDayOfFiscalYear), FLOOR((@x + 7 )/ 7);
		SET @x += 1;
	END;

--Insert Month Numbers.
	WITH MonthSpec AS (
		SELECT
			MonthNbr = DENSE_RANK() OVER (ORDER BY T.N) 
			,StartWeekNbr = 1 + ISNULL(SUM(N.value('.', 'tinyint')) OVER (ORDER BY N ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)
			,EndWeekNbr = SUM(N.value('.', 'tinyint')) OVER (ORDER BY N)
		FROM @WeeksPerMonth_XML.nodes('/x') AS T (N)
	)
		UPDATE FP
		SET MonthNbr = MonthSpec.MonthNbr
		FROM
			#ai_FiscalPeriods FP
			INNER JOIN MonthSpec ON FP.WeekNbr BETWEEN MonthSpec.StartWeekNbr AND MonthSpec.EndWeekNbr;
-- Insert Quarter and Semester Numbers.
	WITH QuarterSpec AS (
		SELECT
			QuarterNbr = DENSE_RANK() OVER (ORDER BY T.N)
			,StartMonthNumber = 1 + ISNULL(SUM(N.value('.', 'tinyint')) OVER (ORDER BY N ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)
			,EndMonthNumber = SUM(N.value('.', 'tinyint')) OVER (ORDER BY N)
		FROM  @MonthsPerQuarter_XML.nodes('/x')  AS T(N)
	)
		UPDATE FP
		SET QuarterNbr = QuarterSpec.QuarterNbr,
			SemesterNbr = CASE WHEN QuarterSpec.QuarterNbr <= 2 THEN 1 ELSE 2 END
		FROM
			#ai_FiscalPeriods FP
			INNER JOIN QuarterSpec ON FP.MonthNbr BETWEEN QuarterSpec.StartMonthNumber AND QuarterSpec.EndMonthNumber;
-- Print a summary of the fiscal periods.
		SELECT @NbrOfDays = COUNT(*) FROM #ai_FiscalPeriods;
		print 'FISCAL YEAR: ' + @FiscalYearName + CHAR(13) +
			'START DATE: ' + CAST(@FirstDayOfFiscalYear AS varchar(20)) + ' ' + DATENAME(dw, @FirstDayOfFiscalYear) + CHAR(13) +
			'TOTAL DAYS: ' + CAST(@NbrOfDays AS varchar(10)) + CHAR(13) +
			'TOTAL WEEKS: ' + CAST(@NbrOfWeeks AS varchar(10)) + CHAR(13) + 'MONTHS:';
	
		DECLARE cur CURSOR  FOR
		SELECT
			MonthSummary = RIGHT(' ' + CAST(FP.MonthNbr AS varchar(10)), 2) + '  ' +
			CAST(MIN(FP.[Date]) AS varchar(10)) + ' ' + LEFT(DATENAME(dw, MIN(FP.[Date])), 3) + '  ' +
			CAST(COUNT(*) AS varchar(10)) +' days  ' +
			CAST(COUNT(DISTINCT FP.WeekNbr) AS varchar(10)) + ' weeks  Qtr: ' +
			CAST(MIN(FP.QuarterNbr) AS varchar(10)) + '  Sem: ' +
			CAST(MIN(FP.SemesterNbr) AS varchar(10))
		FROM
			#ai_FiscalPeriods FP
		GROUP BY
			FP.MonthNbr
		ORDER BY
			FP.MonthNbr;
		OPEN cur;
		FETCH NEXT FROM cur INTO @MonthSummary;
		WHILE @@FETCH_STATUS = 0 
		BEGIN
			print @MonthSummary;
			FETCH NEXT FROM cur INTO @MonthSummary;
		END
		CLOSE cur;
		DEALLOCATE cur;

-- Generate Update statements.
		SELECT
			'UPDATE ' + @TableToUpdate + ' SET ' +
				REPLACE(
				REPLACE(REPLACE(REPLACE(REPLACE(
				REPLACE(REPLACE(REPLACE(REPLACE(
				REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@ColumnUpdatePattern,
					'{year}', @FiscalYearName),
					'{wst}',  (SELECT CONVERT(char(8), MIN([Date]), 112) FROM #ai_FiscalPeriods F WHERE F.WeekNbr = FP.WeekNbr)),
					'{wend}', (SELECT CONVERT(char(8), MAX([Date]), 112) FROM #ai_FiscalPeriods F WHERE F.WeekNbr = FP.WeekNbr)),
					'{mst}',  (SELECT CONVERT(char(8), MIN([Date]), 112) FROM #ai_FiscalPeriods F WHERE F.MonthNbr = FP.MonthNbr)),
					'{mend}', (SELECT CONVERT(char(8), MAX([Date]), 112) FROM #ai_FiscalPeriods F WHERE F.MonthNbr = FP.MonthNbr)),
					'{qst}',  (SELECT CONVERT(char(8), MIN([Date]), 112) FROM #ai_FiscalPeriods F WHERE F.QuarterNbr = FP.QuarterNbr)),
					'{qend}', (SELECT CONVERT(char(8), MAX([Date]), 112) FROM #ai_FiscalPeriods F WHERE F.QuarterNbr = FP.QuarterNbr)),
					'{sst}',  (SELECT CONVERT(char(8), MIN([Date]), 112) FROM #ai_FiscalPeriods F WHERE F.SemesterNbr = FP.SemesterNbr)),
					'{send}', (SELECT CONVERT(char(8), MAX([Date]), 112) FROM #ai_FiscalPeriods F WHERE F.SemesterNbr = FP.SemesterNbr)),
					'{d}', CAST(FP.DayNbr AS varchar(10)) ),
					'{w}', CAST(FP.WeekNbr AS varchar(10)) ),
					'{m}', CAST(FP.MonthNbr AS varchar(10)) ),
					'{q}', CAST(FP.QuarterNbr AS varchar(10)) ) ,
					'{s}', CAST(FP.SemesterNbr AS varchar(10)) )  + 
				' WHERE ' + @DateKeyColumn + ' = ' + CONVERT(char(8), FP.[Date], 112) + ';'
		FROM #ai_FiscalPeriods FP
		ORDER BY DayNbr;

	IF OBJECT_ID('tempdb..#ai_FiscalPeriods') IS NOT NULL DROP TABLE #ai_FiscalPeriods;

	END TRY
	BEGIN CATCH
		THROW
	END CATCH
GO