--CREATE VIEW [dbo].[FiscalCalendar] AS
	SELECT
		[Company] = a.[DW_Account]
		,[Fiscal Start Date] = a.[Starting Date]
		,[Fiscal Ending Date] = DATEADD(DAY, -1, LEAD(a.[Starting Date], 1, b.[Fiscal Year Ending Date]) OVER(ORDER BY a.[Starting Date] ASC)) 
		,[Fiscal Year] = DATEPART(YEAR, a.[Starting Date]) 
		,b.[Fiscal Year Start Date]
		,b.[Fiscal Year Ending Date]
		,[Fiscal Month Number] = (
			CASE a.[Name]
				
				WHEN 'January' THEN 1
				WHEN 'February' THEN 2
				WHEN 'March' THEN 3
				WHEN 'April' THEN 4
				WHEN 'May' THEN 5
				WHEN 'June' THEN 6
				WHEN 'July' THEN 7
				WHEN 'August' THEN 8
				WHEN 'September' THEN 9
				WHEN 'October' THEN 10
				WHEN 'November' THEN 11
				WHEN 'December' THEN 12
			END
		)
		,[Fiscal Month Name] = a.[Name]
		,[Fiscal Quarter Number] = (
			CASE a.[Name]				
				WHEN 'January' THEN 1
				WHEN 'February' THEN 1
				WHEN 'March' THEN 1
				WHEN 'April' THEN 2
				WHEN 'May' THEN 2
				WHEN 'June' THEN 2
				WHEN 'July' THEN 3
				WHEN 'August' THEN 3
				WHEN 'September' THEN 3
				WHEN 'October' THEN 4
				WHEN 'November' THEN 4
				WHEN 'December' THEN 4
			END
		)
		,[Fiscal Quarter Name] = ( 
			CASE a.[Name]				
				WHEN 'January' THEN 'Q1'
				WHEN 'February' THEN 'Q1'
				WHEN 'March' THEN 'Q1'
				WHEN 'April' THEN 'Q2'
				WHEN 'May' THEN 'Q2'
				WHEN 'June' THEN 'Q2'
				WHEN 'July' THEN 'Q3'
				WHEN 'August' THEN 'Q3'
				WHEN 'September' THEN 'Q3'
				WHEN 'October' THEN 'Q4'
				WHEN 'November' THEN 'Q4' 
				WHEN 'December' THEN 'Q4'
			END
		)
	FROM
		[Nav_dbo_Accounting Period_R] a
		LEFT OUTER JOIN 
		(
		SELECT
			a.[DW_Account]
			,[Fiscal Year Start Date] = a.[Starting Date]
			,DATEADD(DAY, -1, LEAD(a.[Starting Date], 1) OVER(ORDER BY a.[Starting Date])) AS [Fiscal Year Ending Date] 
		FROM
			[Nav_dbo_Accounting Period_R] a
		WHERE
			a.[New Fiscal Year] = 1
		) AS b
		ON
			a.[DW_Account] = b.[DW_Account]
			AND a.[Starting Date] >= b.[Fiscal Year Start Date]
			AND a.[Starting Date] < b.[Fiscal Year Ending Date]

--FILTERS

--WHERE DATEPART(YEAR, a.[Starting Date])  = 2015
--AND a.[DW_Account] = 'CRONUS JetCorp USA'