打卡表只有个单纯的某岗位、某卡号和刷卡时间的记录。现要根据岗位和卡号自动识别签到签退。
规则为：
某天、某卡号在某岗位第一次刷卡识别为签到，第二次识别为签退。一日可多次签到、签退，总之奇数次为签到，偶数次为签退。
如打卡表：
岗位 卡号 刷卡时间
07 103 2015-11-10 11:07:04.000
07 103 2015-11-10 11:22:44.000
07 104 2015-11-10 11:07:31.000
07 104 2015-11-10 11:23:02.000
09 110 2015-11-11 08:57:09.000
09 110 2015-11-12 08:55:58.000
09 110 2015-11-12 09:00:22.000
09 110 2015-11-12 10:00:35.000
转换为：
岗位 卡号 签到时间 签退时间
07 103 2015-11-10 11:07:04.000 2015-11-10 11:22:44.000
07 104 2015-11-10 11:07:31.000 2015-11-10 11:23:02.000
09 110 2015-11-11 08:57:09.000
09 110 2015-11-12 08:55:58.000 2015-11-12 09:00:22.000
09 110 2015-11-12 10:00:35.000
菜鸟刚半条腿入门sql，请教大神指点！
------解决思路----------------------
with T(pos,num,tim) as
(select '07', '103', '2015-11-10 11:07:04.000' union all select
'07', '103', '2015-11-10 11:22:44.000' union all select
'07', '104', '2015-11-10 11:07:31.000' union all select
'07', '104', '2015-11-10 11:23:02.000' union all select
'09', '110','2015-11-11 08:57:09.000' union all select
'09', '110', '2015-11-12 08:55:58.000' union all select
'09', '110', '2015-11-12 09:00:22.000' union all select
'09', '110', '2015-11-12 10:00:35.000'),
T1 as (select *,rn=ROW_NUMBER() over (partition by pos,num,convert(varchar(10),tim,120) order by tim) from T),
T2 as (select * from T1 where rn%2=1),
T3 as (select * from T1 where rn%2=0)
select T2.pos as 岗位,T2.num as 卡号,T2.tim as 签到时间,T3.tim as 签退时间
from T2 left join T3 on t2.pos=t3.pos and t2.num=t3.num and convert(varchar(10),t2.tim,120)=convert(varchar(10),t3.tim,120) and t2.rn+1=t3.rn
------解决思路----------------------
CREATE TABLE #T_A(
岗位 varchar(10)
, 卡号 varchar(10)
, 刷卡时间 varchar(30)
)
INSERT INTO #T_A
SELECT '07','103','2015-11-10 11:07:04.000'
UNION ALL
SELECT '07','103','2015-11-10 11:22:44.000'
UNION ALL
SELECT '07','104','2015-11-10 11:07:31.000'
UNION ALL
SELECT '07','104','2015-11-10 11:23:02.000'
UNION ALL
SELECT '09','110','2015-11-11 08:57:09.000'
UNION ALL
SELECT '09','110','2015-11-12 08:55:58.000'
UNION ALL
SELECT '09','110','2015-11-12 09:00:22.000'
UNION ALL
SELECT '09','110','2015-11-12 10:00:35.000'
--select * from #T_A
select * ,ROW_NUMBER() OVER(PARTITION BY 岗位,卡号,left(刷卡时间,10) ORDER BY 刷卡时间) as rn into #aa from #T_A
--select * from #aa
select a.岗位,a.卡号,a.刷卡时间 as '签到时间',isnull(b.刷卡时间,'') as '签退时间'
from
(
select 岗位,卡号,刷卡时间,rn from #aa where rn%2<>0
) a left join
(
select 岗位,卡号,刷卡时间,rn from #aa where rn%2=0
) b on a.岗位=b.岗位 and a.卡号=b.卡号 and left(a.刷卡时间,10)=left(b.刷卡时间,10)
and b.rn-a.rn=1
drop table #aa
drop table #T_A
/*
(8 行受影响)
(8 行受影响)
岗位 卡号 签到时间 签退时间
---------- ---------- ------------------------------ ------------------------------
07 103 2015-11-10 11:07:04.000 2015-11-10 11:22:44.000
07 104 2015-11-10 11:07:31.000 2015-11-10 11:23:02.000
09 110 2015-11-11 08:57:09.000
09 110 2015-11-12 08:55:58.000 2015-11-12 09:00:22.000
09 110 2015-11-12 10:00:35.000
(5 行受影响)
*/