How to Efficiently Populate a Calendar Table with 100 Years of Date Data?

How to efficiently populate a calendar table containing 100 years of date data

Question:

You need to efficiently populate a calendar spanning 100 years with date, year, month, week and date information.

Answer:

Consider using a specialized script to create a more comprehensive calendar, such as the one provided by Microsoft:

SET DATEFIRST 1;
WITH Dates(Date)
AS
(
SELECT cast('1999' AS DateTime) -- SQL Server支持ISO 8601格式
UNION ALL
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast('2099' AS DateTime) -1
),
DatesAndThursdayInWeek(Date, Thursday)
AS
(
SELECT
Date,
CASE DATEPART(weekday,Date)
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date - 1
WHEN 6 THEN Date - 2
WHEN 7 THEN Date - 3
END AS Thursday
FROM Dates
),
Weeks(Week, Thursday)
AS
(
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week, Thursday
FROM DatesAndThursdayInWeek
WHERE DATEPART(weekday,Date) = 4
)
INSERT INTO Auxiliary.Calendar
SELECT
d.Date,
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
w.Week,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,

YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,

CASE
    WHEN (DATEPART(DayOfYear, d.Date) = 1)          -- 元旦
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-7)  -- 棕枝主日
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-3)  -- 圣周四
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-2)  -- 受难日
    OR (d.Date = Auxiliary.Computus(YEAR(Date)))    -- 复活节
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) -- 耶稣升天节
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) -- 五旬节
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) -- 圣灵降临节
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      -- 劳动节
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     -- 宪法日
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    -- 圣诞节
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    -- 节礼日
    THEN 'HOLIDAY'
    WHEN DATEPART(Weekday, d.Date) = 6 THEN 'SATURDAY'
    WHEN DATEPART(Weekday, d.Date) = 7 THEN 'SUNDAY'
    ELSE 'BANKDAY'
END KindOfDay,
CASE
    WHEN (DATEPART(DayOfYear, d.Date) = 1)            THEN '元旦'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-7)  THEN '棕枝主日'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-3)  THEN '圣周四'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-2)  THEN '受难日'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date)))    THEN '复活节'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+39) THEN '耶稣升天节'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+49) THEN '五旬节'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+50) THEN '圣灵降临节'
    WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      THEN '劳动节'
    WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     THEN '宪法日'
    WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    THEN '圣诞节'
    WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    THEN '节礼日'
END Description

FROM DatesAndThursdayInWeek d
-- 此连接用于将周数添加到结果集中
     inner join Weeks w
     on d.Thursday = w.Thursday

OPTION(MAXRECURSION 0)
GO

The above is the detailed content of How to Efficiently Populate a Calendar Table with 100 Years of Date Data?. For more information, please follow other related articles on the PHP Chinese website!