处理考勤打卡记录有关问题

处理考勤打卡记录问题
--打卡记录表
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

--分数不够到时候再补，先放100分！


[解决办法]
测试下以下笨方法的速度如何（特别注意：我直接删除了OriginalData标的数据，如果这时你的原始表，需要保存数据的，请用个临时表，并倒入OriginalData标的所有数据）

update a
set OnDuty1=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime> =a.CheckDate and CheckTime <dateadd(day,1,a.CheckDate)),
OnDuty4=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime> =dateadd(day,1,a.CheckDate) and CheckTime <dateadd(day,2,a.CheckDate))
from OnOffDutyData a

delete a
from OriginalData a
where not exists (
select 1 from OriginalData
where EmployeeID=a.EmployeeID and
convert(Nchar(10),CheckTime,120)=convert(Nchar(10),a.CheckTime,120)
and CheckTime <a.CheckTime
)

update a
set OffDuty1=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime> =a.CheckDate and CheckTime <dateadd(day,1,a.CheckDate))
from OnOffDutyData a

delete a
from OriginalData a
where not exists (
select 1 from OriginalData
where EmployeeID=a.EmployeeID and
convert(Nchar(10),CheckTime,120)=convert(Nchar(10),a.CheckTime,120)
and CheckTime <a.CheckTime
)

update a
set OnDuty2=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime> =a.CheckDate and CheckTime <dateadd(day,1,a.CheckDate))
from OnOffDutyData a

delete a
from OriginalData a
where not exists (
select 1 from OriginalData
where EmployeeID=a.EmployeeID and
convert(Nchar(10),CheckTime,120)=convert(Nchar(10),a.CheckTime,120)
and CheckTime <a.CheckTime
)

update a
set OffDuty2=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime> =a.CheckDate and CheckTime <dateadd(day,1,a.CheckDate))
from OnOffDutyData a

delete a
from OriginalData a
where not exists (
select 1 from OriginalData
where EmployeeID=a.EmployeeID and
convert(Nchar(10),CheckTime,120)=convert(Nchar(10),a.CheckTime,120)
and CheckTime <a.CheckTime
)

update a
set OnDuty3=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime> =a.CheckDate and CheckTime <dateadd(day,1,a.CheckDate))
from OnOffDutyData a

delete a
from OriginalData a
where not exists (
select 1 from OriginalData
where EmployeeID=a.EmployeeID and
convert(Nchar(10),CheckTime,120)=convert(Nchar(10),a.CheckTime,120)
and CheckTime <a.CheckTime
)

update a
set OffDuty3=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime> =a.CheckDate and CheckTime <dateadd(day,1,a.CheckDate))
from OnOffDutyData a

delete a
from OriginalData a
where not exists (
select 1 from OriginalData
where EmployeeID=a.EmployeeID and
convert(Nchar(10),CheckTime,120)=convert(Nchar(10),a.CheckTime,120)
and CheckTime <a.CheckTime
)

[解决办法]
结合下job,view做做。。
[解决办法]
------------看看这个用的是两个表变量，然后直接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
[解决办法]
--总体思想,先对时间进行排序,再就结果存到temp table中

--打卡记录表
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 varchar(10),OffDuty1 varchar(10)
,OnDuty2 varchar(10),OffDuty2 varchar(10)
,OnDuty3 varchar(10),OffDuty3 varchar(10)
,OnDuty4 varchar(10),OffDuty4 varchar(10))
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 * ,left(right(convert(varchar(23),checkTime,120),8),5) FROM OriginalData
--SELECT * FROM OnOffDutyData
-- update result
update OnOffDutyData
set
onDuty1=(select top 1 left(right(convert(varchar(23),checkTime,120),8),5) FROM OriginalData b
where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkTime)=0 order by checkTime )

-- 先对时间进行排序,存到一个temp表中,提高查询速度
select * ,layer=(select count(1) from OriginalData where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkTime)=0 and checkTime <=b.checkTime)
into #temp from OriginalData b

----最终结果
select EmployeeID,CheckDate,
OnDuty1=isnull((select left(right(convert(varchar(23),checkTime,120),8),5) from #temp where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkDate)=0 and layer=1),null),
offDuty1=isnull((select left(right(convert(varchar(23),checkTime,120),8),5) from #temp where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkDate)=0 and layer=2),null),
OnDuty2=isnull((select left(right(convert(varchar(23),checkTime,120),8),5) from #temp where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkDate)=0 and layer=3),null),
offDuty2=isnull((select left(right(convert(varchar(23),checkTime,120),8),5) from #temp where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkDate)=0 and layer=4),null),
OnDuty3=isnull((select left(right(convert(varchar(23),checkTime,120),8),5) from #temp where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkDate)=0 and layer=5),null),
offDuty3=isnull((select left(right(convert(varchar(23),checkTime,120),8),5) from #temp where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkDate)=-1 and layer=1),null)
from OnOffDutyData b

--drop table
drop table #temp
drop table OnOffDutyData
drop table OriginalData

[解决办法]
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
------解决方案--------------------