Cacher is the code snippet organizer for pro developers

-- powerbi.Dates
-- 
-- Date Filters
--
--
--
--
--------------------------------------------------------------------------------
-- Change History
--------------------------------------------------------------------------------
-- Version  Date       	Author				Description
-- -------  ---------- 	-----------			------------------------------------
--  1.0	    12/12/2016  Dave DuVarney		Initial Creation
--  1.1	    02/08/2017  Dave DuVarney       Added Fiscal Attributes
--------------------------------------------------------------------------------

CREATE VIEW [powerbi].[Dates]
AS
	--Use this to control you date range.  Might need modification if you want a maximum date past today.
	WITH DateRanges AS (
		SELECT 
			CAST(CAST(GETDATE() AS DATE) AS DATETIME) [Today]
			, CAST('1/1/2013' AS DATETIME) [Fist Date]
			, CAST(CAST(CAST(GETDATE() AS DATE) AS DATETIME) AS INT)  [Today Sequence]
			, CAST(CAST('1/1/2013' AS DATETIME) AS INT) [First Date Sequence]
			, 1 [Fiscal Year Start Month]
		
	)

	, Digits AS (
		SELECT ones.n Digit
		FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n)
	)

	, FullSequences AS (
		SELECT (ten_thousands.Digit * 10000) + (thousands.Digit * 1000) + (hundreds.Digit * 100) + (tens.Digit * 10) + ones.Digit Sequence
	FROM Digits ones, Digits tens, Digits hundreds, Digits thousands, Digits ten_thousands
	)

	, BaseDates AS (
		SELECT
			fs.Sequence
			, CAST(fs.Sequence AS DATETIME) [Date]
		FROM FullSequences fs
			INNER JOIN DateRanges dr ON fs.Sequence BETWEEN dr.[First Date Sequence] AND dr.[Today Sequence]
	)

	, BaseSet AS (
		SELECT
			Sequence [Date Sequence]
			, [Date]
			, YEAR([Date]) [Year]
			, DATEPART(QUARTER, [Date]) [Quarter Number]
			, MONTH([Date]) [Month Number]
			, DATEPART(WEEK, [Date]) [Week Number]
			, CASE 
				WHEN MONTH([Date]) >= [Fiscal Year Start Month] THEN YEAR([Date]) + 1
				ELSE YEAR([Date])
			END [Fiscal Year]
			, CEILING(
				CAST(
					CASE 
						WHEN MONTH([Date]) < [Fiscal Year Start Month] THEN (MONTH([Date]) - [Fiscal Year Start Month]) + 13
						ELSE (MONTH([Date]) - [Fiscal Year Start Month]) + 1
					END AS DECIMAL
				) / 3
			) [Fiscal Quarter Number]
			, CASE 
				WHEN MONTH([Date]) < [Fiscal Year Start Month] THEN (MONTH([Date]) - [Fiscal Year Start Month]) + 13
				ELSE (MONTH([Date]) - [Fiscal Year Start Month]) + 1
			END [Fiscal Month Number]
		FROM BaseDates, DateRanges
	)
	
	, Quarters AS (
		SELECT
			[Year]
			, [Quarter Number]
			, ROW_NUMBER() OVER(ORDER BY [Year], [Quarter Number]) [Quarter Sequence]
		FROM BaseSet
		GROUP BY [Year], [Quarter Number]
		
	)

	, Months AS (
		SELECT
			[Year]
			, [Month Number]
			, ROW_NUMBER() OVER(ORDER BY [Year], [Month Number]) [Month Sequence]
		FROM BaseSet
		GROUP BY [Year], [Month Number]
	)
	
	, Weeks AS (
		SELECT
			[Year]
			, [Week Number]
			, ROW_NUMBER() OVER(ORDER BY [Year], [Week Number]) [Week Sequence]
		FROM BaseSet
		GROUP BY [Year], [Week Number]
	)

	, FiscalQuarters AS (
		SELECT
			[Fiscal Year]
			, [Fiscal Quarter Number]
			, ROW_NUMBER() OVER(ORDER BY [Fiscal Year], [Fiscal Quarter Number]) [Fiscal Quarter Sequence]
		FROM BaseSet
		GROUP BY [Fiscal Year], [Fiscal Quarter Number]
	)

	, FiscalMonths AS (
		SELECT
			[Fiscal Year]
			, [Fiscal Month Number]
			, ROW_NUMBER() OVER(ORDER BY [Fiscal Year], [Fiscal Month Number]) [Fiscal Month Sequence]
		FROM BaseSet
		GROUP BY [Fiscal Year], [Fiscal Month Number]
	)
	
	SELECT
		 b.[Date]
		, b.[Date Sequence]
		, DATEPART (d, [Date]) [Day] 
		, DATEPART(dw, [Date]) [Day of Week Number]
		, DATENAME(dw, [Date]) [Day of Week]
		, b.[Year]
		, q.[Quarter Sequence]
		, b.[Quarter Number]
		, 'Quarter ' + CAST(b.[Quarter Number] AS VARCHAR) [Quarter]
		, 'Q' + CAST(b.[Quarter Number] AS VARCHAR) [Quarter Short Name]
		, 'Q' + CAST(b.[Quarter Number] AS VARCHAR) + ' - ' + RIGHT(b.[Year], 2) [Quarter - Year]
		, m.[Month Sequence]
		, b.[Month Number]
		, DATENAME(m, [Date]) [Month]
		, LEFT(DATENAME(m, [Date]), 3) [Month Short Name]
		, LEFT(DATENAME(m, [Date]), 3) + ' - ' + RIGHT(b.[Year], 2) [Month - Year]
		, w.[Week Sequence]
		, b.[Week Number]
		, 'Week ' + CAST(b.[Week Number] AS VARCHAR) [Week]
		, 'W' + CAST(b.[Week Number] AS VARCHAR) [Week Short Name]
		, 'W' + CAST(b.[Week Number] AS VARCHAR) + ' - ' + RIGHT(b.[Year], 2) [Week - Year]
		, b.[Fiscal Year]
		, fq.[Fiscal Quarter Sequence]
		, b.[Fiscal Quarter Number]
		, 'Fiscal Quarter ' + CAST(b.[Fiscal Quarter Number] AS VARCHAR) [Fiscal Quarter]
		, 'FQ' + CAST(b.[Fiscal Quarter Number] AS VARCHAR) [Fiscal Quarter Short Name]
		, 'FQ' + CAST(b.[Fiscal Quarter Number] AS VARCHAR) + ' - ' + RIGHT(b.[Fiscal Year], 2) [Fiscal Quarter - Year]
		, fm.[Fiscal Month Sequence]
		, b.[Fiscal Month Number]
		, DATENAME(m, [Date]) [Fiscal Month]
		, LEFT(DATENAME(m, [Date]), 3) [Fiscal Month Short Name]
		, LEFT(DATENAME(m, [Date]), 3) + ' - ' + RIGHT(b.[Fiscal Year], 2) [Fiscal Month - Year]
		, CASE WHEN b.[Date Sequence] = dr.[Today Sequence] THEN 'Yes' ELSE 'No' END [Current Date]
	FROM DateRanges dr, BaseSet b
		INNER JOIN Quarters q ON b.[Year] = q.[Year] AND b.[Quarter Number] = q.[Quarter Number]
		INNER JOIN Months m ON b.[Year] = m.[Year] AND b.[Month Number] = m.[Month Number]
		INNER JOIN Weeks w ON b.[Year] = w.[Year] AND b.[Week Number] = w.[Week Number]
		INNER JOIN FiscalQuarters fq ON b.[Fiscal Year] = fq.[Fiscal Year] AND b.[Fiscal Quarter Number] = fq.[Fiscal Quarter Number]
		INNER JOIN FiscalMonths fm ON b.[Fiscal Year] = fm.[Fiscal Year] AND b.[Fiscal Month Number] = fm.[Fiscal Month Number]
	

GO