时间统计

Question

create table #tmp(empid int ,AttnDate Datetime, Fstyle Int)insert into #tmp
select 1,'2008-01-02 09:00:01',0
union all
select 1,'2008-01-02 12:00:02',1
union all
select 1,'2008-01-02 14:00:00',0
union all
select 1,'2008-01-02 17:35:00',1
union all
select 2,'2008-01-02 09:00:00',0
union all
select 2,'2008-01-02 12:00:00',1
union all
select 2,'2008-01-02 14:00:00',0
union all
select 2,'2008-01-02 17:35:00',1
union all
select 3,'2008-01-02 09:00:00',0
union all
select 3,'2008-01-02 12:00:00',1
union all
select 3,'2008-01-02 14:00:00',0得出结果如下：
 
EMP   上班时间               下班时间           上班时间               下班时间              上班总时间
1,'2008-01-02 09:00:00','2008-01-02 12:00:00','2008-01-02 14:00:00','2008-01-02 17:35:01' ,7.5
2,'2008-01-02 09:00:00','2008-01-02 12:00:00','2008-01-02 14:00:00','2008-01-02 17:35:01' ,7.5
2,'2008-01-02 09:00:00','2008-01-02 12:00:00','2008-01-02 14:00:00', null   ,3

Answer 1

得出结果如下： 
  
EMP   上班时间               下班时间           上班时间               下班时间              上班总时间 
1,'2008-01-02 09:00:00','2008-01-02 12:00:00','2008-01-02 14:00:00','2008-01-02 17:35:01' ,7.5 
2,'2008-01-02 09:00:00','2008-01-02 12:00:00','2008-01-02 14:00:00','2008-01-02 17:35:01' ,7.5 
3,'2008-01-02 09:00:00','2008-01-02 12:00:00','2008-01-02 14:00:00', null                  ,3

Answer 2

select * , datediff(hour , 上班时间1 , 下班时间1) + datediff(hour , 上班时间2 , 下班时间2) 上班总时间 from
(
select empid , 
  max(case px when 1 then attndate end) 上班时间1,
  max(case px when 2 then attndate end) 下班时间1,
  max(case px when 3 then attndate end) 上班时间2,
  max(case px when 4 then attndate end) 下班时间2
from
(
  select * , px = (select count(1) from tb where empid = t.empid and convert(varchar(10),attndate,120) = convert(varchar(10),t.attndate,120) and attndate < t.attndate) + 1 from tb t
) m
group by empid , convert(varchar(10),attndate,120) 
) ndrop table tb/*
empid       上班时间1                                                  下班时间1                                                  上班时间2                                                  下班时间2                                                  上班总时间       
----------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ----------- 
1           2008-01-02 09:00:01.000                                2008-01-02 12:00:02.000                                2008-01-02 14:00:00.000                                2008-01-02 17:35:00.000                                6
2           2008-01-02 09:00:00.000                                2008-01-02 12:00:00.000                                2008-01-02 14:00:00.000                                2008-01-02 17:35:00.000                                6
3           2008-01-02 09:00:00.000                                2008-01-02 12:00:00.000                                2008-01-02 14:00:00.000                                NULL                                                   NULL（所影响的行数为 3 行）
*/

Answer 3

谢谢楼主，你的方法只能正确到小时 (hour),而我要正确到分钟。

Answer 4

create table tb(empid int ,AttnDate Datetime, Fstyle Int) insert into tb 
select 1,'2008-01-02 09:00:01',0 
union all 
select 1,'2008-01-02 12:00:02',1 
union all 
select 1,'2008-01-02 14:00:00',0 
union all 
select 1,'2008-01-02 17:35:00',1 
union all 
select 2,'2008-01-02 09:00:00',0 
union all 
select 2,'2008-01-02 12:00:00',1 
union all 
select 2,'2008-01-02 14:00:00',0 
union all 
select 2,'2008-01-02 17:35:00',1 
union all 
select 3,'2008-01-02 09:00:00',0 
union all 
select 3,'2008-01-02 12:00:00',1 
union all 
select 3,'2008-01-02 14:00:00',0 
select * , cast((case when 下班时间1 is null then 0 else datediff(mi , 上班时间1 , 下班时间1) end + case when 下班时间2 is null then 0 else datediff(mi , 上班时间2 , 下班时间2) end)/30*1.0/2 as decimal(18,1)) 上班总时间 from
(
select empid , 
  max(case px when 1 then attndate end) 上班时间1,
  max(case px when 2 then attndate end) 下班时间1,
  max(case px when 3 then attndate end) 上班时间2,
  max(case px when 4 then attndate end) 下班时间2
from
(
  select * , px = (select count(1) from tb where empid = t.empid and convert(varchar(10),attndate,120) = convert(varchar(10),t.attndate,120) and attndate < t.attndate) + 1 from tb t
) m
group by empid , convert(varchar(10),attndate,120) 
) ndrop table tb/*
empid       上班时间1                                                  下班时间1                                                  上班时间2                                                  下班时间2                                                  上班总时间                
----------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ -------------------- 
1           2008-01-02 09:00:01.000                                2008-01-02 12:00:02.000                                2008-01-02 14:00:00.000                                2008-01-02 17:35:00.000                                6.5
2           2008-01-02 09:00:00.000                                2008-01-02 12:00:00.000                                2008-01-02 14:00:00.000                                2008-01-02 17:35:00.000                                6.5
3           2008-01-02 09:00:00.000                                2008-01-02 12:00:00.000                                2008-01-02 14:00:00.000                                NULL                                                   3.0（所影响的行数为 3 行）
*/

Answer 5

create table tb(empid int ,AttnDate Datetime, Fstyle Int) insert into tb 
select 1,'2008-01-02 09:00:01',0 
union all 
select 1,'2008-01-02 12:00:02',1 
union all 
select 1,'2008-01-02 14:00:00',0 
union all 
select 1,'2008-01-02 17:35:00',1 
union all 
select 2,'2008-01-02 09:00:00',0 
union all 
select 2,'2008-01-02 12:00:00',1 
union all 
select 2,'2008-01-02 14:00:00',0 
union all 
select 2,'2008-01-02 17:35:00',1 
union all 
select 3,'2008-01-02 09:00:00',0 
union all 
select 3,'2008-01-02 12:00:00',1 
union all 
select 3,'2008-01-02 14:00:00',0 
select empid , 上班时间1 上班时间 , 下班时间1 下班时间 , 上班时间2 上班时间1 , 下班时间2 下班时间1,
cast((case when 下班时间1 is null then 0 else datediff(mi , 上班时间1 , 下班时间1) end + case when 下班时间2 is null then 0 else datediff(mi , 上班时间2 , 下班时间2) end)/30*1.0/2 as decimal(18,1)) 上班总时间 from
(
  select empid , 
    max(case px when 1 then attndate end) 上班时间1,
    max(case px when 2 then attndate end) 下班时间1,
    max(case px when 3 then attndate end) 上班时间2,
    max(case px when 4 then attndate end) 下班时间2
  from
  (
    select * , px = (select count(1) from tb where empid = t.empid and convert(varchar(10),attndate,120) = convert(varchar(10),t.attndate,120) and attndate < t.attndate) + 1 from tb t
  ) m
  group by empid , convert(varchar(10),attndate,120) 
) ndrop table tb/*
empid       上班时间                                                   下班时间                                                   上班时间1                                                  下班时间1                                                  上班总时间                
----------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ -------------------- 
1           2008-01-02 09:00:01.000                                2008-01-02 12:00:02.000                                2008-01-02 14:00:00.000                                2008-01-02 17:35:00.000                                6.5
2           2008-01-02 09:00:00.000                                2008-01-02 12:00:00.000                                2008-01-02 14:00:00.000                                2008-01-02 17:35:00.000                                6.5
3           2008-01-02 09:00:00.000                                2008-01-02 12:00:00.000                                2008-01-02 14:00:00.000                                NULL                                                   3.0（所影响的行数为 3 行）
*/

Answer 6

--搞定.
create table tb(empid int ,AttnDate Datetime, Fstyle Int) insert into tb 
select 1,'2008-01-02 09:00:01',0 
union all 
select 1,'2008-01-02 12:00:02',1 
union all 
select 1,'2008-01-02 14:00:00',0 
union all 
select 1,'2008-01-02 17:35:00',1 
union all 
select 2,'2008-01-02 09:00:00',0 
union all 
select 2,'2008-01-02 12:00:00',1 
union all 
select 2,'2008-01-02 14:00:00',0 
union all 
select 2,'2008-01-02 17:35:00',1 
union all 
select 3,'2008-01-02 09:00:00',0 
union all 
select 3,'2008-01-02 12:00:00',1 
union all 
select 3,'2008-01-02 14:00:00',0 
select empid , 上班时间1 上班时间 , 下班时间1 下班时间 , 上班时间2 上班时间 , 下班时间2 下班时间,
cast((case when 下班时间1 is null then 0 else datediff(mi , 上班时间1 , 下班时间1) end + case when 下班时间2 is null then 0 else datediff(mi , 上班时间2 , 下班时间2) end)/30*1.0/2 as decimal(18,1)) 上班总时间 from
(
  select empid , 
    max(case px when 1 then attndate end) 上班时间1,
    max(case px when 2 then attndate end) 下班时间1,
    max(case px when 3 then attndate end) 上班时间2,
    max(case px when 4 then attndate end) 下班时间2
  from
  (
    select * , px = (select count(1) from tb where empid = t.empid and convert(varchar(10),attndate,120) = convert(varchar(10),t.attndate,120) and attndate < t.attndate) + 1 from tb t
  ) m
  group by empid , convert(varchar(10),attndate,120) 
) ndrop table tb/*
empid 上班时间                下班时间                上班时间               下班时间                 上班总时间
----- ----------------------- ----------------------- ----------------------------------------------- ----------
1     2008-01-02 09:00:01.000 2008-01-02 12:00:02.000 2008-01-02 14:00:00.000 2008-01-02 17:35:00.000 6.5
2     2008-01-02 09:00:00.000 2008-01-02 12:00:00.000 2008-01-02 14:00:00.000 2008-01-02 17:35:00.000 6.5
3     2008-01-02 09:00:00.000 2008-01-02 12:00:00.000 2008-01-02 14:00:00.000 NULL 3.0（所影响的行数为 3 行）
*/

Answer 7

结果没对齐,是CSDN排版的问题.

Answer 8

个人觉得这样算有点问题！如下：create table tb(empid int ,AttnDate Datetime, Fstyle Int) insert into tb 
select 1,'2008-01-02 08:59:01',0   ---上班卡多打了一次 
union all 
select 1,'2008-01-02 09:00:01',0    
union all 
select 1,'2008-01-02 12:00:02',1 
union all 
select 1,'2008-01-02 14:00:00',0 
union all 
select 1,'2008-01-02 17:35:00',1 
union all 
select 2,'2008-01-02 09:00:00',0 
/*
union all 
select 2,'2008-01-02 12:00:00',1    ---打卡少打了中间两次
union all 
select 2,'2008-01-02 14:00:00',0 
*/
union all 
select 2,'2008-01-02 17:35:00',1 
union all 
select 3,'2008-01-02 09:00:00',0 
union all 
select 3,'2008-01-02 12:00:00',1            
union all 
select 3,'2008-01-02 14:00:00',0 
select empid , 上班时间1 上班时间 , 下班时间1 下班时间 , 上班时间2 上班时间1 , 下班时间2 下班时间1,
cast((case when 下班时间1 is null then 0 else datediff(mi , 上班时间1 , 下班时间1) end + case when 下班时间2 is null then 0 else datediff(mi , 上班时间2 , 下班时间2) end)/30*1.0/2 as decimal(18,1)) 上班总时间 from
(
  select empid , 
    max(case px when 1 then attndate end) 上班时间1,
    max(case px when 2 then attndate end) 下班时间1,
    max(case px when 3 then attndate end) 上班时间2,
    max(case px when 4 then attndate end) 下班时间2
  from
  (
    select * , px = (select count(1) from tb where empid = t.empid and convert(varchar(10),attndate,120) = convert(varchar(10),t.attndate,120) and attndate < t.attndate) + 1 from tb t
  ) m
  group by empid , convert(varchar(10),attndate,120) 
) ndrop table tb
/*
empid       上班时间                                                   下班时间                                                   上班时间1                                                  下班时间1                                                  上班总时间                
----------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ -------------------- 
1 2008-01-02 08:59:01.000 2008-01-02 09:00:01.000 2008-01-02 12:00:02.000 2008-01-02 14:00:00.000 2.0               
2 2008-01-02 09:00:00.000 2008-01-02 17:35:00.000 NULL NULL 8.5
3 2008-01-02 09:00:00.000 2008-01-02 12:00:00.000 2008-01-02 14:00:00.000 NULL 3.0
*/
存在如下问题：
empid 1     多打了一次上班卡 08：59         上班时间由原本6.5小时，变成 2小时
empid 3     漏打了中间两次12：00，14：00    上班时间由原本2小时，  变成 8.5小时楼主有考虑过这些问题吗？员工不可能都规规矩矩 按照你的模式去打卡，计算上班不能这么算?

Answer 9

更正：
存在如下问题： 
empid 1     多打了一次上班卡 08：59         上班时间由原本6.5小时，变成 2小时 
empid 2     漏打了中间两次12：00，14：00    上班时间由原本6.5小时，  变成 8.5小时 楼主有考虑过这些问题吗？员工不可能都规规矩矩 按照你的模式去打卡，计算上班不能这么算? 

Answer 10

bqb
----------------------------
个人觉得你考虑复杂了,如果要按你这么算,就那天那个帖,你连哪个是上班,哪个是下班都没法知道.

Answer 11

我把那个帖给你看看.http://topic.csdn.net/u/20080310/09/edbcc51b-ae14-453f-aa47-7d3658eba1e1.html?1516005376

Answer 12

谢谢，还有个问题，如果打卡次数多余四次
create table #tmp(empid int ,AttnDate Datetime, Fstyle Int)insert into #tmp
select 1,'2008-01-02 09:00:01',0
union all
select 1,'2008-01-02 12:00:02',1
union all
select 1,'2008-01-02 14:00:00',0
union all
select 1,'2008-01-02 17:35:00',1
union all
select 1,'2008-01-02 19:35:00',0
union all
select 1,'2008-01-02 21:35:00',1
如下，有怎样处理，重点是算出总时间

Answer 13

create table #tmp(empid int ,AttnDate Datetime, Fstyle Int) insert into #tmp 
select 1,'2008-01-02 09:00:01',0 
union all 
select 1,'2008-01-02 12:00:02',1 
union all 
select 1,'2008-01-02 14:00:00',0 
union all 
select 1,'2008-01-02 17:35:00',1 
union all 
select 2,'2008-01-02 09:00:00',0 
union all 
select 2,'2008-01-02 12:00:00',1 
union all 
select 2,'2008-01-02 14:00:00',0 
union all 
select 2,'2008-01-02 17:35:00',1 
union all 
select 3,'2008-01-02 09:00:00',0 
union all 
select 3,'2008-01-02 12:00:00',1 
union all 
select 3,'2008-01-02 14:00:00',0 SELECT a.empid,a.AMDat1 as 上午上班时间,b.AMDat2 as 上午下班时间,a.PMDat1 as 下午上班时间,b.PMDat2 as 下午下班时间,
(
select 
cast((select datediff(minute,a.AMDat1,b.AMDat2)
+(select datediff(minute,a.PMDat1,b.PMDat2)))/60 as varchar)
+'.'+
(select cast(((
select datediff(minute,a.AMDat1,b.AMDat2)+(select datediff(minute,a.PMDat1,b.PMDat2))
)%60)/6 as varchar))
)上班时间
FROM 
(select empid,min(AttnDate)AMDat1,max(AttnDate)PMDat1 from #tmp where Fstyle = 0 group by empid)a,
(select empid,min(AttnDate)AMDat2,max(AttnDate)PMDat2 from #tmp where Fstyle = 1 group by empid)b 
WHERE A.EMPID = B.EMPID

Answer 14

create table tb(empid int ,AttnDate Datetime, Fstyle Int) insert into tb 
select 1,'2008-01-02 09:00:01',0 
union all 
select 1,'2008-01-02 12:00:02',1 
union all 
select 1,'2008-01-02 14:00:00',0 
union all 
select 1,'2008-01-02 17:35:00',1 
union all 
select 2,'2008-01-02 09:00:00',0 
union all 
select 2,'2008-01-02 12:00:00',1 
union all 
select 2,'2008-01-02 14:00:00',0 
union all 
select 2,'2008-01-02 17:35:00',1 
union all 
select 3,'2008-01-02 09:00:00',0 
union all 
select 3,'2008-01-02 12:00:00',1 
union all 
select 3,'2008-01-02 14:00:00',0 
select empid , 上班时间1 上班时间 , 下班时间1 下班时间 , 上班时间2 上班时间 , 下班时间2 下班时间,
cast((case when 下班时间1 is null then 0 else datediff(mi , 上班时间1 , 下班时间1) end + case when 下班时间2 is null then 0 else datediff(mi , 上班时间2 , 下班时间2) end)/30*1.0/2 as decimal(18,1)) 上班总时间 from
(
  select empid , 
    max(case px when 1 then attndate end) 上班时间1,
    max(case px when 2 then attndate end) 下班时间1,
    max(case px when 3 then attndate end) 上班时间2,
    max(case px when 4 then attndate end) 下班时间2
  from
  (
    select * , px = (select count(1) from tb where empid = t.empid and convert(varchar(10),attndate,120) = convert(varchar(10),t.attndate,120) and attndate < t.attndate) + 1 from tb t
  ) m
  group by empid , convert(varchar(10),attndate,120) 
) n

Answer 15

刚才发的贴子修改一下!SELECT a.empid,a.AMDat1 as 上午上班时间,b.AMDat2 as 上午下班时间,a.PMDat1 as 下午上班时间,c.PMDat2 as 下午下班时间,
(
select 
cast((select datediff(minute,a.AMDat1,b.AMDat2)
+(select datediff(minute,a.PMDat1,c.PMDat2)))/60 as varchar)
+'.'+
(select cast(((
select datediff(minute,a.AMDat1,b.AMDat2)+(select datediff(minute,a.PMDat1,c.PMDat2))
)%60)/6 as varchar))
)上班时间
FROM 
(select empid,min(AttnDate)AMDat1,max(AttnDate)PMDat1 from #tmp where Fstyle = 0 group by empid)a,
(select empid,min(AttnDate)AMDat2 from #tmp where Fstyle = 1 group by empid)b,
(select empid,max(AttnDate)PMDat2 from #tmp group by empid)c
WHERE A.EMPID = B.EMPID and A.EMPID = c.EMPID

Answer 16

create table #tmp(empid int ,AttnDate Datetime, Fstyle Int) insert into #tmp 
select 1,'2008-01-02 09:00:01',0 
union all 
select 1,'2008-01-02 09:00:15',0 
union all 
select 1,'2008-01-02 09:00:30',0 
union all 
select 1,'2008-01-02 12:00:02',1 
union all 
select 1,'2008-01-02 14:00:00',0 
union all 
select 1,'2008-01-02 17:35:00',1 怎样用一条sql语句，将间隔少于5分钟的记录删除，就是将union all 
select 1,'2008-01-02 09:00:15',0 
union all 
select 1,'2008-01-02 09:00:30',0  这两条数据删除了。

Answer 17

delete t
from #tmp t
where exists (
select 1 from #tmp
where empid=a.empid and Fstyle=a.Fstyle
and AttnDate<a.AttnDate
and datediff(mi,AttnDate,a.AttnDate)<5
)

Answer 18

不测试就有错create table #tmp(empid int ,AttnDate Datetime, Fstyle Int)  insert into #tmp  
select 1,'2008-01-02 09:00:01',0  
union all  
select 1,'2008-01-02 09:00:15',0  
union all  
select 1,'2008-01-02 09:00:30',0  
union all  
select 1,'2008-01-02 12:00:02',1  
union all  
select 1,'2008-01-02 14:00:00',0  
union all  
select 1,'2008-01-02 17:35:00',1  delete t
from #tmp t
where exists (
select 1 from #tmp
where empid=t.empid and Fstyle=t.Fstyle
and AttnDate<t.AttnDate
and datediff(mi,AttnDate,t.AttnDate)<5
)select * from #tmp--结果
empid       AttnDate                                               Fstyle      
----------- ------------------------------------------------------ ----------- 
1           2008-01-02 09:00:01.000                                0
1           2008-01-02 12:00:02.000                                1
1           2008-01-02 14:00:00.000                                0
1           2008-01-02 17:35:00.000                                1（所影响的行数为 4 行）