Dates.Period

by

Purpose

Date table for Financial Year

Source

Spreadsheet of Period Dates

Parameters

Date_Key
Company

Sample Exec

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

Response

[Date_Key]  = convert(date, mydate),
[company_id]    = [src].[company],
[Financial_Year]     = [src].[ACCOUNTING_YEAR],
[Period_Number]     = format(cast([src].[ACCOUNTING_PERIOD] as int),'00'),
[Period_Name] = src.[desc],
[FQ_Number]     = cast((([src].[ACCOUNTING_PERIOD]-1)/3)+1 as int),
[FQ_Name] = CASE
                WHEN cast((([src].[ACCOUNTING_PERIOD]-1)/3)+1 as int) = 1 THEN 'First'
                WHEN cast((([src].[ACCOUNTING_PERIOD]-1)/3)+1 as int) = 2 THEN 'Second'
                WHEN cast((([src].[ACCOUNTING_PERIOD]-1)/3)+1 as int) = 3 THEN 'Third'
                WHEN cast((([src].[ACCOUNTING_PERIOD]-1)/3)+1 as int) = 4 THEN 'Fourth'
            END,
[FY_Dayof] =    DATEDIFF(DAY,year_start.Period_Start,mydate)+1,
[FY_Weekof] = cast(((DATEDIFF(DAY,year_start.Period_Start,mydate))/7)+1 as int),
[FQ_Dayof] =    DATEDIFF(DAY,qtr_start.Period_Start,mydate)+1, 
[FQ_Weekof] = cast(((DATEDIFF(DAY,qtr_start.Period_Start,mydate))/7)+1 as int),
[FYYY-FP]           = concat_ws('-',[src].[ACCOUNTING_YEAR],format(cast([src].[ACCOUNTING_PERIOD] as int),'00')),
[FYYY-FQ]           = concat_ws('-',[src].[ACCOUNTING_YEAR],format(cast((([src].[ACCOUNTING_PERIOD]-1)/3)+1 as int),'00')),
[Period_Firstof]    = src.Period_Start,
[Period_Lastof] = src.Period_End,
[FQ_Firstof]    = qtr_start.Period_Start, 
[FQ_Lastof] = qtr_end.Period_End,   
[FY_Firstof]    = year_start.Period_Start,
[FY_Lastof] = year_end.Period_End

SQL

BMT-DWH-DEV/Dates_Period.sql

1 thought on “Dates.Period”

Leave a Comment