Dates.Dim_Date

Purpose

Combined Date Table (myDate & Period) filtered for single company

Sample Exec

EXEC get.myView @dataMart = 'Dates', @viewName = 'Dim_Date', @token = '<myToken>', @version = '<202404>'

Parameters

[Date_Key]

Response

[Date_Key]            = CONVERT(date, mydate),
[Date_asText]        = REPLACE(CONVERT(VARCHAR, mydate, 106),' ','-'),
[Date_asInt]        = CONVERT(VARCHAR,  mydate, 112),

[Week_Number]        = FORMAT(DATEPART(WEEK, mydate),'00'),
[ISO_Week]            = FORMAT(DATEPART(ISO_WEEK, mydate),'00'),
[Weekday]            = DATEPART(WEEKDAY, mydate),
[Weekday_Name]        = DATENAME(WEEKDAY, mydate),
[Month_Dayof]        = FORMAT(DATEPART(DAY,  mydate),'00'),
[Month]                = FORMAT(DATEPART(MONTH,    mydate),'00'),
[Month_Name]        = DATENAME(MONTH,   mydate),
[Month_Label]        = FORMAT(mydate,'MMM'),
[Year]                = DATEPART(YEAR,    mydate),
[Year_Dayof]        = DATEPART(DAYOFYEAR,   mydate),
[myView_Dayof] =    DATEDIFF(DAY,convert(date, cast(year(getdate())-7 as varchar(10))+'-10-01' ),mydate)+1,

[YYYY-WW]           = concat_ws('-',DATEPART(YEAR,  mydate),format(DATEPART(WEEK,   mydate),'00')), 
[YYYY-MM]           = concat_ws('-',DATEPART(YEAR,  mydate),format(DATEPART(MONTH,  mydate),'00')), 

[Week_Firstof]  = DATEADD(DAY, 1-DATEPART(WEEKDAY, mydate), CONVERT(date, mydate)),
[Week_Lastof]       = DATEADD(DAY, 7-DATEPART(WEEKDAY, mydate), CONVERT(date, mydate)),
[Month_Firstof] = DATEFROMPARTS(YEAR(mydate), MONTH(mydate), 1),
[Month_Lastof]  = EOMONTH(mydate),
[Year_Firstof]  = CONVERT(date,DATEADD(yy, DATEDIFF(yy, 0,  mydate), 0)),
[Year_Lastof]   = CONVERT(date,DATEADD(yy, DATEDIFF(yy, 0, mydate) + 1, -1))

Holiday

[is_Weekend] = iif(mydate.[Weekday] in (1,7),1,0),
[is_Holiday] = iif(len(hols.Bank_holiday) > 0,1,0),
[is_9to5working] = iif((mydate.Weekday in (1,7)) or (len(hols.Bank_holiday) > 0),0,1),
[Holiday_Name] = hols.Bank_holiday

Period

,[Financial_Year]
,[Period_Number]
,[Period_Name]
,[FQ_Number]
,[FQ_Name]
,'P' + [Period_Number] + [Financial_Year] as [Submission_Period],
,[FY_Dayof]
,[FY_Weekof]
,[FQ_Dayof]
,[FQ_Weekof]
	,CASE
		when [FQ_Weekof] between 1 and 5 then 1
		when [FQ_Weekof] between 6 and 9 then 2
	ELSE
		3
	END AS [FQ_Monthof]
,[FYYY-FP]
,[FYYY-FQ]
,[FYYY-FW]
,[Period_Firstof]
,[Period_Lastof]
,[FQ_Firstof]
,[FQ_Lastof]
,[FY_Firstof]
,[FY_Lastof]

SQL

BMT-DWH-DEV/Dates_Dim_Date.sql