最近在做一个考勤系统
数据库读取的考勤数据如下：
现在问题来了，如何用SQL语句转换成如下格式：
深入问题：
1.如果中间忘记打卡如何空出一格？
2.如果重复打卡怎么消除重复数据？
------解决思路----------------------
自由班次，必须规定一下基本规则：
1)上下班不能跨末个时点（SQL 中为 04:00）
2)最多打卡6次（次数可以扩充，总之得固定）
由于没有班次规则，忘记打卡只能靠人工检查后补记录。
WITH CheckInOut(UserID,CheckTime) AS (
SELECT 1,Convert(datetime,'2014-10-06 09:03.43',120) UNION ALL
SELECT 1,Convert(datetime,'2014-10-06 12:01:07',120) UNION ALL
SELECT 1,Convert(datetime,'2014-10-06 13:57:12',120) UNION ALL
SELECT 1,Convert(datetime,'2014-10-06 13:58:00',120) UNION ALL
SELECT 1,Convert(datetime,'2014-10-06 18:05:34',120) UNION ALL
SELECT 1,Convert(datetime,'2014-10-06 19:29:59',120) UNION ALL
SELECT 1,Convert(datetime,'2014-10-06 23:15:28',120) UNION ALL
SELECT 1,Convert(datetime,'2014-10-07 08:50:51',120) UNION ALL
SELECT 1,Convert(datetime,'2014-10-07 12:04:41',120) UNION ALL
SELECT 1,Convert(datetime,'2014-10-07 13:56:39',120) UNION ALL
SELECT 1,Convert(datetime,'2014-10-07 18:01:55',120) UNION ALL
SELECT 1,Convert(datetime,'2014-10-07 19:35:16',120) UNION ALL
SELECT 1,Convert(datetime,'2014-10-08 00:15:00',120) --跨日下班
)
,t1 AS ( -- 单独求出日期。按时间求序列号。
SELECT UserID,
Convert(datetime,
Convert(varchar(10),
DateAdd(hour,-4,CheckTime), -- 04:00 之前算前一天的打卡
120),
120) AS Date,
CheckTime,
ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY CheckTime) rn1
FROM CheckInOut
)
,t2 AS ( -- 按日期分组求序列号。60秒内算连续打卡，只留前1条。
SELECT UserID,
Date,
CheckTime,
ROW_NUMBER() OVER(PARTITION BY UserID,Date ORDER BY CheckTime) rn2
FROM t1
WHERE NOT EXISTS (SELECT *
FROM t1 t
WHERE t.UserID = t1.UserID
AND t.rn1 = t1.rn1+1
AND DateDiff(second,t1.CheckTime,t.CheckTime)<=60
)
)
SELECT UserID,
Date,
[1] AS [上班1],
[2] AS [下班1],
[3] AS [上班2],
[4] AS [下班2],
[5] AS [上班3],
[6] AS [下班3],
( DateDiff(second,[1],[2])
+ DateDiff(second,[3],[4])
+ DateDiff(second,[5],[6])
) / 3600.0 WorkHours
FROM t2
PIVOT (
MAX(CheckTime)
FOR rn2 IN ([1], [2], [3], [4], [5], [6])
) AS p
UserID Date 上班1 下班1 上班2 下班2 上班3 下班3 WorkHours
------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ---------------------------------------
1 2014-10-06 00:00:00.000 2014-10-06 09:03:00.430 2014-10-06 12:01:07.000 2014-10-06 13:58:00.000 2014-10-06 18:05:34.000 2014-10-06 19:29:59.000 2014-10-06 23:15:28.000 10.852777
1 2014-10-07 00:00:00.000 2014-10-07 08:50:51.000 2014-10-07 12:04:41.000 2014-10-07 13:56:39.000 2014-10-07 18:01:55.000 2014-10-07 19:35:16.000 2014-10-08 00:15:00.000 11.980555