34,593
社区成员
发帖
与我相关
我的任务
分享
-- 初始化班次表
INSERT INTO 班次表
SELECT 1,'甲班','2015-10-19 07:00','2015-10-19 19:00',12,36 UNION ALL
SELECT 2,'乙班','2015-10-19 19:00','2015-10-20 07:00',12,36 UNION ALL
SELECT 3,'丙班','2015-10-20 07:00','2015-10-20 19:00',12,36 UNION ALL
SELECT 4,'早班','2015-10-19 07:00','2015-10-19 19:00',12,24 UNION ALL
SELECT 5,'晚班','2015-10-19 19:00','2015-10-20 07:00',12,24 UNION ALL
SELECT 6,'白班','2015-10-19 08:00','2015-10-19 17:00',8,24
-- 以 @班次ID为参数,统计考勤记录、递进时间到下个班次
DECLARE @班次ID int
;WITH a AS ( -- 用标准工时的一半来划分上下班区段
SELECT *,
CONVERT(datetime,
CONVERT(varchar(10),GetDate(),120),
120) 日期,
DATEADD(hour,-标准工时/2,上班) 上班开始,
DATEADD(hour,标准工时/2,上班) 上班结束,
DATEADD(hour,-标准工时/2,下班) 下班开始,
DATEADD(hour,标准工时/2,下班) 下班结束
FROM 班次表
WHERE 班次ID = @班次ID
)
,b AS (
SELECT a.日期,
e.员工ID,
e.班次ID,
a.上班开始,
a.上班结束,
a.下班开始,
a.下班结束
FROM a
JOIN 员工表 e
ON a.班次ID = e.班次ID
)
,c1 AS (
SELECT b.日期,
b.员工ID,
b.班次ID,
MIN(d.打卡时间) 上班时间
FROM b
LEFT JOIN 打卡明细 d
ON b.员工ID = d.员工ID
AND d.打卡时间 >= b.上班开始
AND d.打卡时间 < b.上班结束
)
,c2 AS (
SELECT b.日期,
b.员工ID,
b.班次ID,
MAX(d.打卡时间) 下班时间
FROM b
LEFT JOIN 打卡明细 d
ON b.员工ID = d.员工ID
AND d.打卡时间 >= b.下班开始
AND d.打卡时间 < b.下班结束
)
,c AS (
SELECT c1.日期,
c1.员工ID,
c1.班次ID,
c1.上班时间,
c2.下班时间,
CASE WHEN c1.上班时间 IS NULL THEN
NULL
WHEN c2.下班时间 IS NULL THEN
NULL
ELSE
DATEDIFF(minute,c1.上班时间,c2.下班时间)/60
END 工时
FROM c1
JOIN c2
ON c1.员工ID = c2.员工ID
)
INSERT INTO 考勤记录(日期,员工ID,班次ID,上班时间,下班时间,工时,出勤状态)
SELECT c.*,
CASE WHEN ISNULL(c.工时,0) >= a.标准工时 THEN
1
ELSE
0
END -- 如果要更加细分出勤状态,用上班时间、下班时间进行判断
FROM c, a
UPDATE 班次表
SET 上班 = DATEADD(hour,间隔,上班),
下班 = DATEADD(hour,间隔,下班)
WHERE 班次ID = @班次ID