Bagaimanakah saya boleh membuat jadual kalendar SQL yang komprehensif menjangkau 100 tahun, termasuk cuti dan maklumat fiskal?

Buat jadual kalendar yang menjangkau 100 tahun dalam SQL

Skrip SQL berikut menunjukkan cara menjana jadual kalendar yang menjangkau 100 tahun:

<code class="language-sql">IF EXISTS (SELECT * FROM information_schema.tables WHERE Table_Name = 'Calendar' AND Table_Type = 'BASE TABLE')
BEGIN
DROP TABLE [Calendar]
END

CREATE TABLE [Calendar]
(
    [CalendarDate] DATETIME
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(d, 36500, @StartDate)

WHILE @StartDate <= @EndDate
      BEGIN
             INSERT INTO [Calendar]
             (
                   CalendarDate
             )
             SELECT
                   @StartDate

             SET @StartDate = DATEADD(dd, 1, @StartDate)
      END</code>

Skrip Kalendar Lanjutan

Skrip kalendar yang lebih komprehensif kelihatan seperti ini:

<code class="language-sql">CREATE SCHEMA Auxiliary;
-- 将辅助表等内容放在单独的模式中
-- SQL Server 2005 的新增功能之一
go

CREATE FUNCTION Auxiliary.Computus
-- Computus（拉丁语，意为计算）用于计算基督教日历中复活节的日期
-- http://en.wikipedia.org/wiki/Computus
-- 我使用的是 Meeus/Jones/Butcher 格里高利算法
(
    @Y INT -- 我们要计算复活节星期日的年份
)
RETURNS DATETIME
AS
BEGIN
DECLARE
@a INT,
@b INT,
@c INT,
@d INT,
@e INT,
@f INT,
@g INT,
@h INT,
@i INT,
@k INT,
@L INT,
@m INT

SET @a = @Y % 19
SET @b = @Y / 100
SET @c = @Y % 100
SET @d = @b / 4
SET @e = @b % 4
SET @f = (@b + 8) / 25
SET @g = (@b - @f + 1) / 3
SET @h = (19 * @a + @b - @d - @g + 15) % 30
SET @i = @c / 4
SET @k = @c % 4
SET @L = (32 + 2 * @e + 2 * @i - @h - @k) % 7
SET @m = (@a + 11 * @h + 22 * @L) / 451
RETURN(DATEADD(month, ((@h + @L - 7 * @m + 114) / 31)-1, cast(cast(@Y AS VARCHAR) AS Datetime)) + ((@h + @L - 7 * @m + 114) % 31))
END
GO


CREATE TABLE [Auxiliary].[Calendar] (
-- 这是日历表
  [Date] datetime NOT NULL,
  [Year] int NOT NULL,
  [Quarter] int NOT NULL,
  [Month] int NOT NULL,
  [Week] int NOT NULL,
  [Day] int NOT NULL,
  [DayOfYear] int NOT NULL,
  [Weekday] int NOT NULL,
  [Fiscal_Year] int NOT NULL,
  [Fiscal_Quarter] int NOT NULL,
  [Fiscal_Month] int NOT NULL,
  [KindOfDay] varchar(10) NOT NULL,
  [Description] varchar(50) NULL,
  PRIMARY KEY CLUSTERED ([Date])
)
GO

ALTER TABLE [Auxiliary].[Calendar]
-- 以 Celko 风格，我非常重视约束（从不使用它们 ;-))
-- http://www.celko.com/

ADD CONSTRAINT [Calendar_ck] CHECK (  ([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week]  BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN ('HOLIDAY', 'SATURDAY', 'SUNDAY', 'BANKDAY')))
GO




SET DATEFIRST 1;
-- 我希望我的表包含符合 ISO 8601 的日期数据
-- http://en.wikipedia.org/wiki/ISO_8601
-- 因此一周的第一天是星期一
WITH Dates(Date)
-- 一个递归 CTE，生成 1999 年到 2020-12-31 之间的所有日期
AS
(
SELECT cast('1999' AS DateTime) Date -- SQL Server 支持 ISO 8601 格式，因此这是一个 1999-01-01 的明确快捷方式
UNION ALL                            -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast('2021' 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)
-- 现在我们为星期四生成星期数
-- ROW_NUMBER 是 SQL Server 2005 的新增功能
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,

-- 挪威的财政年度可能与实际年度不同，它们是相同的
-- http://en.wikipedia.org/wiki/Fiscal_year
YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,

CASE
-- 挪威的节假日
-- 对于其他国家和地区：维基百科 - 按国家/地区列出的节假日
-- http://en.wikipedia.org/wiki/List_of_holidays_by_country
    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 'New Year''s Day'</code>

Atas ialah kandungan terperinci Bagaimanakah saya boleh membuat jadual kalendar SQL yang komprehensif menjangkau 100 tahun, termasuk cuti dan maklumat fiskal?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!