透過一句 SQL 建立時間資料表

–取自 Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model    P. 294

DECLARE @StartYear AS INT = 2000 ;
DECLARE @EndYear AS INT = 2006 ;

WITH Years
    AS ( SELECT YYYY = @StartYear
    UNION ALL
    SELECT YYYY + 1
    FROM Years
    WHERE YYYY < @EndYear
    ),
Months
    AS ( SELECT MM = 1
    UNION ALL
    SELECT MM + 1
    FROM Months
    WHERE MM < 12
    ),
Days
    AS ( SELECT DD = 1
    UNION ALL
    SELECT DD + 1
    FROM Days
    WHERE DD < 31
    ),
DatesRaw
    AS ( SELECT YYYY = YYYY,
    MM = MM,
    DD = DD,
    ID_Date = YYYY * 10000 + MM * 100 + DD,
    Date = CASE WHEN ISDATE(YYYY * 10000 + MM * 100 + DD) = 1
    THEN CAST(CAST(YYYY * 10000 + MM * 100
    + DD AS VARCHAR) AS DATE)
    ELSE NULL
    END
    FROM Years
    CROSS JOIN Months
    CROSS JOIN Days
    WHERE ISDATE(YYYY * 10000 + MM * 100 + DD) = 1
    )
SELECT d.ID_Date,
    d.Date,
    [Year] = YEAR(d.Date),
    MonthNumber = MONTH(d.Date),
    [Month] = DATENAME(MONTH, d.Date),
    DayOfMonth = DAY(d.Date),
    DayOfWeekNumber = DATEPART(dw, d.Date),
    [DayOfWeek] = DATENAME(dw, d.Date),
    WorkingDay = CAST(CASE DATEPART(dw, d.Date)
    WHEN 1 THEN 0 — Sunday
    WHEN 7 THEN 0 — Saturday
    ELSE 1 — Might lookup for a holidays table here
    END AS BIT)
FROM DatesRaw d
ORDER BY d.Date

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: