在SQL中建立跨越100年的日曆表
以下SQL腳本示範如何產生一個跨越100年的日曆表：
<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>進階日曆腳本
更全面的日曆腳本如下：
<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>以上是如何建立涵蓋 100 年的綜合 SQL 日曆表，包括假期和財務資訊？的詳細內容。更多資訊請關注PHP中文網其他相關文章！
