-- Create_View.Reference.vw_Dates.sql

USE [Dates]
GO

/*
-- check
SELECT x.* FROM [Reference].[vw_Dates] AS x (NOLOCK);
*/

DROP VIEW IF EXISTS [Reference].[vw_Dates]
GO

CREATE VIEW [Reference].[vw_Dates]
AS

SELECT 
	-- General
	[Date Key]						= d.DateKey,
	[Date Text]						= d.DateText,
	[Date]							= d.DateDate,

	-- Year
	[Year]							= d.YearNumber,

	-- Quarter
	[Quarter Number]				= d.QuarterNumber,
	[Quarter]						= d.QuarterEnglish,
	[Year Quarter Number]			= d.YearQuarterNumber,

	-- Month
	[Month Number]					= d.MonthNumber,
	[Month]							= d.MonthEnglish,
	[Month Short]					= d.MonthShortEnglish,
	[Month Initial]					= d.MonthInitialEnglish,
	[YearMonthNumber]				= d.YearMonthNumber,
	
	-- Day
	[Day Of Month Number]			= d.DayOfMonthNumber,
	[Day Of Week Number]			= d.DayOfWeekNumber,
	[Day]							= d.DayEnglish,
	[Day Short]						= d.DayShortEnglish,
	[Day Initial]					= d.DayInitialEnglish,

	-- Fiscal Year
	[Fiscal Year Number]			= d.FiscalYearNumber,
	[Fiscal Year]					= d.FiscalYear,

	-- Fiscal Quarter
    [Fiscal Quarter Number]			= d.FiscalQuarterNumber,
    [Fiscal Quarter]				= d.FiscalQuarterEnglish,
	[Fiscal Year Quarter Number]	= d.FiscalYearQuarterNumber,

	-- Holiday
	[Holiday Name]					= d.HolidayNameEnglish,

	-- Flags (fixed)
	[IsWeekday]						= d.IsWeekday,
	[IsWeekendDay]					= d.IsWeekendDay,
	[IsBusinessDay]					= d.IsBusinessDay,
	[IsHoliday]						= d.IsHoliday,

	-- Offset
	[Year Offset]			= d.YearOffset,
	[Fiscal Year Offset]	= d.FiscalYearOffset,
	[Quarter Offset]		= d.QuarterOffset,
	[Month Offset]			= d.MonthOffset,
	[Day Offset]			= d.DayOffset,

	-- Flags (calculated)
	[IsAfterToday]			= CASE WHEN d.DateDate > getdate() THEN 1 ELSE 0 END,
	[IsCurrentYear]			= CASE WHEN d.YearNumber = year(getdate()) THEN 1 ELSE 0 END,
	[IsCurrentFY]			= CASE WHEN d.FiscalYearNumber = (year(getdate()) + 1) THEN 1 ELSE 0 END,
	[IsCurrentFQ]			= CASE 
									WHEN d.FiscalYearNumber = (year(getdate()) + 1) 
										AND d.FiscalQuarterNumber = (datepart(quarter, getdate()) - 1) THEN 1 
									ELSE 0 
								END
FROM 
	Reference.Dates AS d (NOLOCK);

GO