11 Ekim 2017 Çarşamba

2008 to 2023 dynamic date range with t-sql - OLAP time dimension table




SET LANGUAGE Turkish

;WITH DaySeries AS
(
    SELECT PK_Date = CAST('2008-01-01' as date)
    UNION ALL
    SELECT DATEADD(DAY,1,PK_Date)
    FROM dayseries
    WHERE DATEADD(DAY,1,PK_Date)<'2023-01-01'
)

SELECT
       Date_ID=ROW_NUMBER() OVER(ORDER BY PK_Date)
       ,*
       ,Day_Name= DATENAME(DAY,PK_Date)
       ,Month_Name= DATENAME(MONTH,PK_Date)
       ,Date_Name =DATENAME(DAY,PK_Date)+' '+DATENAME(MONTH,PK_Date)+' '+DATENAME(DW,PK_Date)
       ,Quarter_Name =CAST(Period_ID as nvarchar(2)) + '.Periyot'
       ,Half_Name =CAST(Half_ID as nvarchar(2)) + '.Yarı'
       ,Week_Of_Year_Name=CAST(Week as nvarchar(2)) +'.Hafta'
       FROM (
SELECT --top 730
       PK_Date
          ,Day_ID=DATENAME(DAY,PK_Date)
          ,Month_ID=DATEPART(month,PK_Date)
          ,Year_ID =DATENAME(YEAR,PK_Date)
          ,Period_ID=CEILING((CAST(DATEPART(month,PK_Date) as numeric(6,4))/3))
          ,Half_ID=CEILING((CAST(DATEPART(month,PK_Date) as numeric(6,4))/6))
          ,Week= DATEPART(WEEK,PK_Date)
          ,WeekDay_ID= DATEPART(WEEKDAY,PK_Date)
          ,WorkDay= CASE DATEPART(WEEKDAY,PK_Date) WHEN 6 THEN 0 WHEN 7 THEN 0 ELSE 1 END
FROM dayseries
) as sub OPTION (MAXRECURSION 10000)