DECLARE
@StartDate DATETIME = '20060101'
, @EndDate DATETIME = '20061231'
;WITH DimDate AS(
SELECT DATES = CAST(@StartDate AS DATE)
UNION ALL
SELECT DATES = DATEADD(d, 1, DATES)
FROM DimDate
WHERE DATES < @EndDate
), DimFiscalCount AS(
SELECT Cnt = 1
UNION ALL
SELECT Cnt = Cnt + 1
FROM DimFiscalCount
WHERE Cnt < 11
), Fiscal AS(
SELECT
DateKey = CONVERT(VARCHAR(8), DATES, 112)
, FiscalYear = YEAR(DATES) - (CASE WHEN (MONTH(DATES) + (11 - cnt)) % 12 + 1 <> MONTH(DATES) - cnt THEN 1 ELSE 0 END)
, FiscalSemester = CASE WHEN (MONTH(DATES) + (11 - cnt)) % 12 + 1 < 7 THEN 1 ELSE 2 END
, FiscalQuarter = CASE WHEN (MONTH(DATES) + (11 - cnt)) % 12 + 1 < 4 THEN 1
WHEN (MONTH(DATES) + (11 - cnt)) % 12 + 1 < 7 THEN 2
WHEN (MONTH(DATES) + (11 - cnt)) % 12 + 1 < 10 THEN 3
ELSE 4 END
, FiscalMonth = (MONTH(DATES) + (11 - cnt)) % 12 + 1
, CalendarName = 'Fiscal Starting ' + DATENAME(MONTH, DATEADD(m, Cnt, '19000101'))
FROM DimDate
CROSS APPLY DimFiscalCount
)
SELECT
DateKey
, FiscalYear
, FiscalSemester
, FiscalQuarter
, FiscalMonth
, CalendarName
FROM Fiscal
OPTION (MAXRECURSION 0)
|
Desired Output
No comments:
Post a Comment