考勤统计中,一般都需要考虑到节假日信息和调休的日期,每年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′
在此基础上,我们可以做月工作日天数统计,年工作日天数统计,以及年休息日统计,还有其他统计等。同时我们可以根据业务需要,用任何一门语言实现相关的业务需求。
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/119666.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...