有员工多笔刷卡数据张三 20120202 17:00
张三 20120202 17:02李四 20120202 20:00李四 20120202 20:10
李四 20120202 20:38如果员工刷卡, 时间点间隔在5分钟内刷算一笔,如何求出员工一天中刷卡次数上述应该是张三1次, 李四3次
张三 20120202 17:02李四 20120202 20:00李四 20120202 20:10
李四 20120202 20:38如果员工刷卡, 时间点间隔在5分钟内刷算一笔,如何求出员工一天中刷卡次数上述应该是张三1次, 李四3次
算3筆
declare @t table (name varchar(4),time datetime)
insert into @t
select '张三','2012-02-02 17:00' union all
select '张三','2012-02-02 17:02' union all
select '张三','2012-02-02 17:03' union all
select '张三','2012-02-02 17:05' union all
select '张三','2012-02-02 17:11' union all
select '张三','2012-02-02 17:13' union all
select '张三','2012-02-02 17:17' union all
select '李四','2012-02-02 20:00' union all
select '李四','2012-02-02 20:10' union all
select '李四','2012-02-02 20:38'select a.name ,
a.time as t1 ,
b.c1 as t2
into #t
from @t a
left join ( select name ,
dateadd(mi, 5, time) as c1
from @t t
where time = ( select min(time)
from @t
where name = t.name
)
) b on a.name = b.namewhile exists ( select top 1
*
from #t
where t1 > t2 )
begin
update #t
set t2 = b.c1
from #t a
left join ( select name ,
dateadd(mi, 5, t1) as c1
from #t t
where t1 = ( select min(t1)
from #t
where name = t.name
and t1 > t2
)
) b on a.name = b.name
where t1 > t2
endselect name ,count(distinct t2) cnt from #t group by name
drop table #t
/*
name cnt
---- -----------
李四 3
张三 3
*/
declare @t table (name varchar(4),time datetime)
insert into @t
select '张三','2012-02-02 17:00' union all
select '张三','2012-02-02 17:02' union all
select '张三','2012-02-02 17:03' union all
select '张三','2012-02-02 17:05' union all
select '张三','2012-02-02 17:11' union all
select '张三','2012-02-02 17:13' union all
select '张三','2012-02-02 17:17' union all
select '李四','2012-02-02 20:00' union all
select '李四','2012-02-02 20:10' union all
select '李四','2012-02-02 20:38'select a.name ,
a.time as t1 ,
b.c1 as t2
into #t
from @t a
left join ( select name ,
dateadd(mi, 5, time) as c1
from @t t
where time = ( select min(time)
from @t
where name = t.name
)
) b on a.name = b.namewhile exists ( select top 1
*
from #t
where t1 > t2 )
begin
update #t
set t2 = b.c1
from #t a
left join ( select name ,
dateadd(mi, 5, t1) as c1
from #t t
where t1 = ( select min(t1)
from #t
where name = t.name
and t1 > t2
)
) b on a.name = b.name
where t1 > t2
END
--查看这个结果显得比较清楚
select * from #t
/*
name t1 t2
---- ----------------------- -----------------------
张三 2012-02-02 17:00:00.000 2012-02-02 17:05:00.000
张三 2012-02-02 17:02:00.000 2012-02-02 17:05:00.000
张三 2012-02-02 17:03:00.000 2012-02-02 17:05:00.000
张三 2012-02-02 17:05:00.000 2012-02-02 17:05:00.000
张三 2012-02-02 17:11:00.000 2012-02-02 17:16:00.000
张三 2012-02-02 17:13:00.000 2012-02-02 17:16:00.000
张三 2012-02-02 17:17:00.000 2012-02-02 17:22:00.000
李四 2012-02-02 20:00:00.000 2012-02-02 20:05:00.000
李四 2012-02-02 20:10:00.000 2012-02-02 20:15:00.000
李四 2012-02-02 20:38:00.000 2012-02-02 20:43:00.000
*/
select name ,count(distinct t2) cnt from #t group by name
drop table #t
/*
name cnt
---- -----------
李四 3
张三 3
*/
declare @t table (name varchar(4),time datetime)
insert into @t
select '张三','2012-02-02 17:00' union all
select '张三','2012-02-02 17:02' union all
select '李四','2012-02-02 20:00' union all
select '李四','2012-02-02 20:10' union all
select '李四','2012-02-02 20:38'--手动排版
select a.name ,a.time as t1 ,b.c1 as t2 into #t from @t a left join
( select name ,dateadd(mi, 5, time) as c1 from @t t
where time = ( select min(time) from @t where name = t.name)
) b on a.name = b.namewhile exists (select top 1 * from #t where t1 > t2)
begin
update #t set t2 = b.c1 from #t a
left join
(select name ,dateadd(mi, 5, t1) as c1 from #t t
where t1 = (select min(t1) from #t where name = t.name and t1 > t2)
) b on a.name = b.name where t1 > t2
endselect name ,count(distinct t2) cnt from #t group by name
drop table #t/*
name cnt
---- -----------
李四 3
张三 1
*/
GO
INSERT INTO Demo
SELECT '张三','20120202 17:00'
UNION ALL
SELECT '张三','20120202 17:02'
UNION ALL
SELECT '李四','20120202 20:00'
UNION ALL
SELECT '李四','20120202 20:10'
UNION ALL
SELECT '李四','20120202 20:38'
UNION ALL
SELECT '李四','20120202 20:39'
GO
SELECT *
FROM Demo T1
WHERE DATEDIFF(DAY,'2012-02-02 17:00:00.000','2012-02-02')=0
AND NOT EXISTS(SELECT 1 FROM Demo WHERE P1=T1.P1 AND DATEDIFF(N,P2,T1.P2) BETWEEN 0 AND 5 AND P2<>T1.P2)
FROM Demo T1
WHERE DATEDIFF(DAY,'2012-02-02 17:00:00.000','2012-02-02')=0
AND NOT EXISTS(SELECT 1 FROM Demo WHERE P1=T1.P1 AND DATEDIFF(N,P2,T1.P2) BETWEEN 0 AND 5 AND P2<>T1.P2) GROUP BY P1