在SQL中建立跨越100年的日曆表

以下SQL腳本示範(fàn)如何產(chǎn)生一個跨越100年的日曆表：

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

進(jìn)階日曆腳本

更全面的日曆腳本如下：

CREATE SCHEMA Auxiliary;
-- 將輔助表等內(nèi)容放在單獨(dú)的模式中
-- SQL Server 2005 的新增功能之一
go

CREATE FUNCTION Auxiliary.Computus
-- Computus（拉丁語，意為計算）用于計算基督教日歷中復(fù)活節(jié)的日期
-- http://en.wikipedia.org/wiki/Computus
-- 我使用的是 Meeus/Jones/Butcher 格里高利算法
(
    @Y INT -- 我們要計算復(fù)活節(jié)星期日的年份
)
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 風(fēng)格，我非常重視約束（從不使用它們 ;-))
-- 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 的日期數(shù)據(jù)
-- 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)
-- 現(xiàn)在我們?yōu)樾瞧谒纳尚瞧跀?shù)
-- 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,

-- 挪威的財政年度可能與實(shí)際年度不同，它們是相同的
-- 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
-- 挪威的節(jié)假日
-- 對于其他國家和地區(qū)：維基百科 - 按國家/地區(qū)列出的節(jié)假日
-- 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)))    -- 復(fù)活節(jié)星期日
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) -- 耶穌升天節(jié)
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) -- 五旬節(jié)
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) -- 圣靈降臨節(jié)
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      -- 勞動節(jié)
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     -- 憲法日
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    -- 圣誕節(jié)
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    -- 節(jié)禮日
    THEN 'HOLIDAY'
    WHEN DATEPART(Weekday, d.Date) = 6 THEN 'SATURDAY'
    WHEN DATEPART(Weekday, d.Date) = 7 THEN 'SUNDAY'
    ELSE 'BANKDAY'
END KindOfDay,
CASE
-- 挪威節(jié)假日的描述
    WHEN (DATEPART(DayOfYear, d.Date) = 1)            THEN 'New Year''s Day'

以上是如何建立涵蓋 100 年的綜合 SQL 日曆表，包括假期和財務(wù)資訊？的詳細(xì)內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！