Cacher is the code snippet organizer for pro developers

-- Dates
-- 
-- Date Filters
--
--
--
--
--------------------------------------------------------------------------------
-- Change History
--------------------------------------------------------------------------------
-- Version  Date       	Author				Description
-- -------  ---------- 	-----------			------------------------------------
--  1.0		12/12/2016  Dave DuVarney		Initial Creation
--------------------------------------------------------------------------------

CREATE VIEW dbo.Dates
AS
	-- Gets the last transaction date from the Sales view
	WITH DateRanges AS (
		SELECT 
			CAST(CAST(GETDATE() AS DATE) AS DATETIME) [Today]
			--TODO: add start date and update logic if this needs to be boxed in.

	)

	, BaseSet AS (
		SELECT TOP 4000 
			CAST(CAST([Today] - ROW_NUMBER() OVER(ORDER BY c1.object_id) + 1 AS INT) AS DATETIME) [Date]
			, CAST([Today] - ROW_NUMBER() OVER(ORDER BY c1.object_id) + 1 AS INT) [Date Sequence]
			, YEAR(CAST(CAST([Today] - ROW_NUMBER() OVER(ORDER BY c1.object_id) + 1 AS INT) AS DATETIME)) [Year]
			, MONTH(CAST(CAST([Today] - ROW_NUMBER() OVER(ORDER BY c1.object_id) + 1 AS INT) AS DATETIME)) [Month Number]
			, DATEPART(WEEK, CAST(CAST([Today] - ROW_NUMBER() OVER(ORDER BY c1.object_id) + 1 AS INT) AS DATETIME)) [Week Number]
		FROM sys.columns c1, sys.columns c2, DateRanges
	)

	, 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]
	)

	SELECT
		 b.[Date]
		, b.[Date Sequence]
		, DATEPART(dw, [Date]) [Day of Week Number]
		, DATENAME(dw, [Date]) [Day of Week]
		, b.[Year]
		, m.[Month Sequence]
		, b.[Month Number]
		, DATENAME(m, [Date]) [Month]
		, w.[Week Sequence]
		, b.[Week Number]
		, 'Week ' + CAST(b.[Week Number] AS VARCHAR) [Week]
	FROM BaseSet b
		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]