处理考勤打卡记录问题

这几天一直忙处理考勤自动匹配班次问题，比较郁闷拷了几天没有找到一个好的解决办法，只有制作个简单的例子，放分200放到CSDN上，终于在朋友的解答中找到一个解决方法.[@more@]

原问题：

-- 打卡记录表 CREATE TABLE OriginalData( [ id ] int IDENTITY ( 1 , 1 ),EmployeeID int ,CheckTime datetime ) INSERT INTO OriginalData SELECT 1 , ' 2007-06-11 08:01 ' UNION ALL SELECT 1 , ' 2007-06-11 12:02 ' UNION ALL SELECT 1 , ' 2007-06-11 13:05 ' UNION ALL SELECT 1 , ' 2007-06-11 17:40 ' UNION ALL SELECT 1 , ' 2007-06-11 19:00 ' UNION ALL SELECT 1 , ' 2007-06-11 23:42 ' UNION ALL SELECT 1 , ' 2007-06-11 23:58 ' UNION ALL SELECT 1 , ' 2007-06-12 07:50 ' UNION ALL SELECT 1 , ' 2007-06-12 12:00 ' UNION ALL SELECT 3 , ' 2007-06-11 20:00 ' UNION ALL SELECT 3 , ' 2007-06-12 04:00 ' UNION ALL SELECT 3 , ' 2007-06-12 07:55 ' UNION ALL SELECT 3 , ' 2007-06-12 12:00 ' UNION ALL SELECT 3 , ' 2007-06-12 13:00 ' UNION ALL SELECT 3 , ' 2007-06-12 17:35 ' -- 考勤表 CREATE TABLE OnOffDutyData(EmployeeID int ,CheckDate datetime
,OnDuty1 datetime ,OffDuty1 datetime
,OnDuty2 datetime ,OffDuty2 datetime
,OnDuty3 datetime ,OffDuty3 datetime
,OnDuty4 datetime ,OffDuty4 datetime ) INSERT INTO OnOffDutyData (EmployeeID,CheckDate) SELECT 1 , ' 2007-06-11 ' UNION ALL SELECT 1 , ' 2007-06-12 ' UNION ALL SELECT 3 , ' 2007-06-11 ' UNION ALL SELECT 3 , ' 2007-06-12 '
SELECT * FROM OriginalData SELECT * FROM OnOffDutyData /*
想要的初始化结果:
EmployeeID CheckDate OnDuty1 OffDuty1 OnDuty2 OffDuty2 OnDuty3 OffDuty3 OnDuty4
1 2007-06-11 08:01 12:02 13:05 17:40 19:00 23:42 07:50 --(07:50为第2天的第一次打卡记录)
1 2007-06-12 07:50 12:00 NULL NULL NULL NULL NULL
3 2007-06-11 20:00 04:00 （04:00 为第2天第1次打卡，其他段为如上行为NULL）
4 2007-06-12 04:00 07:55 12:00 13:00 17:35 NULL NULL

--方法说明：
按EmployeeID 和 打卡日期 CONVERT(nchar(10),CheckTime,120) ，每天提取当天前6次打卡记录和第2天第一次打卡记录
UPDATE 考勤表(OnOffDutyData)。

打卡记录表数据大小：5000（人）×30（天）×6(大约每天的打卡次数)=90万条记录
考勤表数据大小：5000(人)×30（天）=15万条记录

要求一个能提高效率的Update方法，具体实现方法不限。 */
DROP TABLE OriginalData,OnOffDutyData

解决方法参考：

方法1，来自leo_lesley(leo) ：

-- ----------看看这个用的是两个表变量，然后直接update处理的，不用先删除，再插入。
-- 打卡记录表 CREATE TABLE OriginalData( [ id ] int IDENTITY ( 1 , 1 ),EmployeeID int ,CheckTime datetime ) INSERT INTO OriginalData SELECT 1 , ' 2007-06-11 08:01 ' UNION ALL SELECT 1 , ' 2007-06-11 12:02 ' UNION ALL SELECT 1 , ' 2007-06-11 13:05 ' UNION ALL SELECT 1 , ' 2007-06-11 17:40 ' UNION ALL SELECT 1 , ' 2007-06-11 19:00 ' UNION ALL SELECT 1 , ' 2007-06-11 23:42 ' UNION ALL SELECT 1 , ' 2007-06-11 23:58 ' UNION ALL SELECT 1 , ' 2007-06-12 07:50 ' UNION ALL SELECT 1 , ' 2007-06-12 12:00 ' UNION ALL SELECT 3 , ' 2007-06-11 20:00 ' UNION ALL SELECT 3 , ' 2007-06-12 04:00 ' UNION ALL SELECT 3 , ' 2007-06-12 07:55 ' UNION ALL SELECT 3 , ' 2007-06-12 12:00 ' UNION ALL SELECT 3 , ' 2007-06-12 13:00 ' UNION ALL SELECT 3 , ' 2007-06-12 17:35 '
go
-- 考勤表 CREATE TABLE OnOffDutyData(EmployeeID int ,CheckDate datetime
,OnDuty1 datetime ,OffDuty1 datetime
,OnDuty2 datetime ,OffDuty2 datetime
,OnDuty3 datetime ,OffDuty3 datetime
,OnDuty4 datetime ,OffDuty4 datetime ) INSERT INTO OnOffDutyData (EmployeeID,CheckDate) SELECT 1 , ' 2007-06-11 ' UNION ALL SELECT 1 , ' 2007-06-12 ' UNION ALL SELECT 3 , ' 2007-06-11 ' UNION ALL SELECT 3 , ' 2007-06-12 '
go
declare @t table (EmployeeID int ,CheckDate datetime ,OnDuty1 varchar ( 10 ),OnDuty2 varchar ( 10 ),OnDuty3 varchar ( 10 ),OnDuty4 varchar ( 10 ),OnDuty5 varchar ( 10 ),OnDuty6 varchar ( 10 ),OnDuty7 varchar ( 10 ),OnDuty8 varchar ( 10 )) declare @lsb table (EmployeeID int ,d datetime ,m varchar ( 10 ),cnt int )
insert @lsb SELECT b.EmployeeID,d = convert ( char ( 10 ),b.CheckTime, 21 ),m = right ( convert ( char ( 16 ),b.CheckTime, 21 ), 5 ),
Cnt = ( select count ( 1 ) from OriginalData where EmployeeID = b.EmployeeID and convert ( char ( 10 ),b.CheckTime, 21 ) = convert ( char ( 10 ),CheckTime, 21 ) and CheckTime < b.CheckTime ) FROM OriginalData b
insert @t SELECT a.EmployeeID,CheckDate = convert ( char ( 10 ),a.CheckDate, 21 ),
OnDuty1 = max ( case when a.EmployeeID = b.EmployeeID and convert ( char ( 10 ),a.CheckDate, 21 ) = d and cnt = 0 then m else null end ),
OnDuty1 = isnull ( max ( case when a.EmployeeID = b.EmployeeID and convert ( char ( 10 ),a.CheckDate, 21 ) = d and cnt = 1 then m end ), min ( case when a.EmployeeID = b.EmployeeID and cnt = 0 and convert ( char ( 10 ),a.CheckDate, 21 ) < d then m end )),
OnDuty2 = max ( case when a.EmployeeID = b.EmployeeID and convert ( char ( 10 ),a.CheckDate, 21 ) = d and cnt = 2 then m else null end ),
OnDuty2 = isnull ( max ( case when a.EmployeeID = b.EmployeeID and convert ( char ( 10 ),a.CheckDate, 21 ) = d and cnt = 3 then m end ), min ( case when a.EmployeeID = b.EmployeeID and cnt = 0 and convert ( char ( 10 ),a.CheckDate, 21 ) < d then m end )),
OnDuty3 = max ( case when a.EmployeeID = b.EmployeeID and convert ( char ( 10 ),a.CheckDate, 21 ) = d and cnt = 4 then m else null end ),
OnDuty3 = isnull ( max ( case when a.EmployeeID = b.EmployeeID and convert ( char ( 10 ),a.CheckDate, 21 ) = d and cnt = 5 then m end ), min ( case when a.EmployeeID = b.EmployeeID and cnt = 0 and convert ( char ( 10 ),a.CheckDate, 21 ) < d then m end )),
OnDuty4 = max ( case when a.EmployeeID = b.EmployeeID and convert ( char ( 10 ),a.CheckDate, 21 ) = d and cnt = 6 then m else null end ),
OnDuty4 = isnull ( max ( case when a.EmployeeID = b.EmployeeID and convert ( char ( 10 ),a.CheckDate, 21 ) = d and cnt = 7 then m end ), min ( case when a.EmployeeID = b.EmployeeID and cnt = 0 and convert ( char ( 10 ),a.CheckDate, 21 ) < d then m end )) FROM OnOffDutyData a left join @lsb b on a.EmployeeID = b.EmployeeID group by a.EmployeeID,CheckDate order by a.EmployeeID,CheckDate


updatea set a.OnDuty1 = t.CheckDate + t.OnDuty1,
a.OffDuty1 = case when t.OnDuty1 is not null then t.CheckDate + t.OnDuty2 else null end ,
a.OnDuty2 = t.CheckDate + t.OnDuty3,
a.OffDuty2 = case when t.OnDuty3 is not null then t.CheckDate + t.OnDuty4 else null end ,
a.OnDuty3 = t.CheckDate + t.OnDuty5,
a.OffDuty3 = case when t.OnDuty5 is not null then t.CheckDate + t.OnDuty6 else null end ,
a.OnDuty4 = t.CheckDate + t.OnDuty7,
a.OffDuty4 = case when t.OnDuty7 is not null then t.CheckDate + t.OnDuty8 else null end from OnOffDutyData a, @t t where a.EmployeeID = t.EmployeeID and a.CheckDate = t.CheckDate
select * from OnOffDutyData

drop table OnOffDutyData,OriginalData

方法2，来自hellowork(一两清风)：

-- 打卡记录表 CREATE TABLE OriginalData( [ id ] int IDENTITY ( 1 , 1 ),EmployeeID int ,CheckTime datetime ) INSERT INTO OriginalData SELECT 1 , ' 2007-06-11 08:01 ' UNION ALL SELECT 1 , ' 2007-06-11 12:02 ' UNION ALL SELECT 1 , ' 2007-06-11 13:05 ' UNION ALL SELECT 1 , ' 2007-06-11 17:40 ' UNION ALL SELECT 1 , ' 2007-06-11 19:00 ' UNION ALL SELECT 1 , ' 2007-06-11 23:42 ' UNION ALL -- 没有这句子，结果的第一行记录就有问题，无法记录第2天的第1次刷卡记录 SELECT 1 , ' 2007-06-11 23:58 ' UNION ALL SELECT 1 , ' 2007-06-12 07:50 ' UNION ALL SELECT 1 , ' 2007-06-12 12:00 ' UNION ALL SELECT 3 , ' 2007-06-11 20:00 ' UNION ALL SELECT 3 , ' 2007-06-12 04:00 ' UNION ALL SELECT 3 , ' 2007-06-12 07:55 ' UNION ALL SELECT 3 , ' 2007-06-12 12:00 ' UNION ALL SELECT 3 , ' 2007-06-12 13:00 ' UNION ALL SELECT 3 , ' 2007-06-12 17:35 '
go
-- 考勤表 CREATE TABLE OnOffDutyData(EmployeeID int ,CheckDate datetime
,OnDuty1 datetime ,OffDuty1 datetime
,OnDuty2 datetime ,OffDuty2 datetime
,OnDuty3 datetime ,OffDuty3 datetime
,OnDuty4 datetime ,OffDuty4 datetime ) INSERT INTO OnOffDutyData (EmployeeID,CheckDate) SELECT 1 , ' 2007-06-11 ' UNION ALL SELECT 1 , ' 2007-06-12 ' UNION ALL SELECT 3 , ' 2007-06-11 ' UNION ALL SELECT 3 , ' 2007-06-12 '
go
SELECT EmployeeID,CheckTime,
OnDuty1 = max (OnDuty1),
OnOffDuty1 = ISNULL ( max (OnOffDuty1), max (OnDuty4)),
OnDuty2 = case when max (OnOffDuty1) is null then NULL else ISNULL ( max (OnDuty2), max (OnDuty4)) end ,
OnOffDuty2 = case when max (OnDuty2) is null then NULL else ISNULL ( max (OnOffDuty2), max (OnDuty4)) end ,
OnDuty3 = case when max (OnOffDuty2) is null then NULL else ISNULL ( max (OnDuty3), max (OnDuty4)) end ,
OnOffDuty3 = case when max (OnDuty3) is null then NULL else ISNULL ( max (OnOffDuty3), max (OnDuty4)) end ,
OnDuty4 = case when max (OnOffDuty3) is null then NULL else max (OnDuty4) end FROM

( select EmployeeID,CheckTime = convert ( varchar ( 10 ),CheckTime, 120 ),
OnDuty1 = ( select top 1 convert ( varchar ( 5 ),CheckTime, 108 ) from OriginalData where EmployeeID = t.EmployeeID and datediff (dd,CheckTime,t.CheckTime) = 0 order by CheckTime),
OnOffDuty1 = ( select convert ( varchar ( 5 ),CheckTime, 108 ) from OriginalData as a where EmployeeID = t.EmployeeID and

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7764484/viewspace-918759/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7764484/viewspace-918759/