处理考勤打卡记录问题(200分题目)
原问题：
--打卡记录表
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
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
--打卡记录表
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 datediff(dd,CheckTime,t.CheckTime) = 0 and
(select count(*) from OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 2),
OnDuty2=(select convert(varchar(5),CheckTime,108) from OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,CheckTime,t.CheckTime) = 0 and
(select count(*) from OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 3),
OnOffDuty2=(select convert(varchar(5),CheckTime,108) from OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,CheckTime,t.CheckTime) = 0 and
(select count(*) from OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 4),
OnDuty3=(select convert(varchar(5),CheckTime,108) from OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,CheckTime,t.CheckTime) = 0 and
(select count(*) from OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 5),
OnOffDuty3=(select convert(varchar(5),CheckTime,108) from OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,CheckTime,t.CheckTime) = 0 and
(select count(*) from OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 6),
OnDuty4=(select top 1 convert(varchar(5),CheckTime,108) from OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,t.CheckTime,CheckTime)=1 order by CheckTime)
from OriginalData as t ) AS x
GROUP BY EmployeeID,CheckTime ORDER BY 1,2
drop table OnOffDutyData,OriginalData
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 datediff(dd,CheckTime,t.CheckTime) = 0 and
(select count(*) from OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 2),
OnDuty2=(select convert(varchar(5),CheckTime,108) from OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,CheckTime,t.CheckTime) = 0 and
(select count(*) from OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 3),
OnOffDuty2=(select convert(varchar(5),CheckTime,108) from OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,CheckTime,t.CheckTime) = 0 and
(select count(*) from OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 4),
OnDuty3=(select convert(varchar(5),CheckTime,108) from OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,CheckTime,t.CheckTime) = 0 and
(select count(*) from OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 5),
OnOffDuty3=(select convert(varchar(5),CheckTime,108) from OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,CheckTime,t.CheckTime) = 0 and
(select count(*) from OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 6),
OnDuty4=(select top 1 convert(varchar(5),CheckTime,108) from OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,t.CheckTime,CheckTime)=1 order by CheckTime)
from OriginalData as t ) AS x
GROUP BY EmployeeID,CheckTime ORDER BY 1,2
drop table OnOffDutyData,OriginalData
