考勤統計中，一般都需要考慮到節假日資訊和調休的日期，每年12月份國家會頒布新一年的節假日資訊，我們可根據節假日資訊，維護成相關的日期表，用于日期維護。本文介紹下相關實作方式。
日期表的建立如下：
CREATE TABLE [dbo].[HR_WorkDayList](
[WorkDate] [varchar](10) NOT NULL,
[DateType] [nvarchar](10) NULL,
[IsWorkDay] [bit] NULL,
[Remark] [nvarchar](20) NULL,
CONSTRAINT [PK_HR_WorkDayList] PRIMARY KEY CLUSTERED
(
[WorkDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
可根據系統表插入每年的日期清單，SQL腳本如下（可以一次性生成幾年内的日期清單，在系統設計中，我這裡生成了2018年-2028年的日期資料）：
INSERT INTO [dbo].[HR_WorkDayList](WorkDate,DateType,IsWorkDay,Remark) select WorkDate,DateType,IsWorkDay,Remark from
(SELECT convert(char(10), DATEADD(dd, number, '2023-08-11'),120) AS WorkDate,
datename(weekday,convert(char(10), DATEADD(dd, number, 2023-08-11),120)) as DateType,
case when datename(weekday,convert(char(10), DATEADD(dd, number, 2023-08-11),120))<>'星期六' and datename(weekday,convert(char(10), DATEADD(dd, number, 2023-08-11),120))<>'星期日' then 1 else 0 end as IsWorkDay,
'' as Remark
FROM master.dbo.spt_values as spt
WHERE type = 'p' AND number <= DATEDIFF(DAY, '2023-08-11', '2028-12-31')) as SourceTable
根據每年國家釋出的節假日資訊，更新相關日期。采用Excel表格，組合成SQL腳本，然後更新表資料 。下表是我整理的2019年的節假日資訊。
日期 備注 是否上班 SQL腳本更新
2018-12-29 上班 1 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =1 ,[Remark] ='上班'WHERE WorkDate='2018-12-29'
2018-12-30 元旦 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='元旦'WHERE WorkDate='2018-12-30'
2018-12-31 元旦 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='元旦'WHERE WorkDate='2018-12-31'
2019-01-01 元旦 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='元旦'WHERE WorkDate='2019-01-01'
2019-02-03 上班 1 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =1 ,[Remark] ='上班'WHERE WorkDate='2019-02-03'
2019-02-03 上班 1 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =1 ,[Remark] ='上班'WHERE WorkDate='2019-02-03'
2019-02-04 春節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春節'WHERE WorkDate='2019-02-04'
2019-02-05 春節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春節'WHERE WorkDate='2019-02-05'
2019-02-06 春節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春節'WHERE WorkDate='2019-02-06'
2019-02-07 春節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春節'WHERE WorkDate='2019-02-07'
2019-02-08 春節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春節'WHERE WorkDate='2019-02-08'
2019-02-09 春節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春節'WHERE WorkDate='2019-02-09'
2019-02-10 春節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='春節'WHERE WorkDate='2019-02-10'
2019-04-05 清明節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='清明節'WHERE WorkDate='2019-04-05'
2019-04-06 清明節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='清明節'WHERE WorkDate='2019-04-06'
2019-04-07 清明節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='清明節'WHERE WorkDate='2019-04-07'
2019-05-01 勞動節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='勞動節'WHERE WorkDate='2019-05-01'
2019-06-07 端午節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='端午節'WHERE WorkDate='2019-06-07'
2019-06-08 端午節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='端午節'WHERE WorkDate='2019-06-08'
2019-06-09 端午節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='端午節'WHERE WorkDate='2019-06-09'
2019-09-13 中秋節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='中秋節'WHERE WorkDate='2019-09-13'
2019-09-14 中秋節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='中秋節'WHERE WorkDate='2019-09-14'
2019-09-15 中秋節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='中秋節'WHERE WorkDate='2019-09-15'
2019-09-29 上班 1 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =1 ,[Remark] ='上班'WHERE WorkDate='2019-09-29'
2019-10-12 上班 1 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =1 ,[Remark] ='上班'WHERE WorkDate='2019-10-12'
2019-10-01 國慶節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='國慶節'WHERE WorkDate='2019-10-01'
2019-10-02 國慶節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='國慶節'WHERE WorkDate='2019-10-02'
2019-10-03 國慶節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='國慶節'WHERE WorkDate='2019-10-03'
2019-10-04 國慶節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='國慶節'WHERE WorkDate='2019-10-04'
2019-10-05 國慶節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='國慶節'WHERE WorkDate='2019-10-05'
2019-10-06 國慶節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='國慶節'WHERE WorkDate='2019-10-06'
2019-10-07 國慶節 0 UPDATE [dbo].[HR_WorkDayList] SET [IsWorkDay] =0 ,[Remark] ='國慶節'WHERE WorkDate='2019-10-07'
在此基礎上，我們可以做月工作日天數統計，年工作日天數統計，以及年休息日統計，還有其他統計等。同時我們可以根據業務需要，用任何一門語言實作相關的業務需求。