求教考勤汇总SQL代码
人员考勤原始数据如下：
部门 工号 日期 时间
后勤 A001 2013-11-02 8:00:22
后勤 A001 2013-11-02 8:00:55
后勤 A001 2013-11-02 17:35:22
后勤 A002 2013-11-02 7:59:22
生产 A003 2013-11-02 7:50:01
生产 A003 2013-11-02 19:58:01
生产 A004 2013-11-02 20:00:00
生产 A004 2013-11-03 8:00:00
生产 A005 2013-11-02 20:30:00
------------------------------------------------------------------------------
因排班比较灵活，所以不能提供排班表，输入数据只有
后勤 工作时间 9.5小时
生产 工作时间 12小时
希望通过SQL代码得到如下数据：
部门 工号 日期 时间1 时间2 工作时长 状态
后勤 A001 2013-11-2 8:00:22 17:35:22 9.58 正常
后勤 A002 2013-11-2 7:59:22 --- --- 异常
生产 A003 2013-11-2 7:50:01 19:58:01 12.13 正常
生产 A004 2013-11-2 20:00:00 8:00:00 12 正常
生产 A005 2013-11-2 20:30:00 --- --- 异常
[解决办法]
create table #tb(deptName varchar(10),deptNo varchar(10), cdate datetime )
insert into #tb
select '后勤','A001','2013-11-02 8:00:22'
union all select '后勤','A001','2013-11-02 8:00:55'
union all select '后勤','A001','2013-11-02 17:35:22'
union all select '后勤','A002','2013-11-02 7:59:22'
union all select '生产','A003','2013-11-02 7:50:01'
union all select '生产','A003','2013-11-02 19:58:01'
union all select '生产','A004','2013-11-02 20:00:00'
union all select '生产','A004','2013-11-02 8:00:00'
union all select '生产','A005','2013-11-02 20:30:00'
select deptname 部门,deptno as 工号,cdate as 日期,convert(varchar(8),xdate,108) as 时间1
,case when convert(varchar(8),xdate,108)=convert(varchar(8),mdate,108) then '--' else convert(varchar(8),mdate,108) end as 时间2
,case when convert(varchar(8),xdate,108)=convert(varchar(8),mdate,108) then '--' else cast(round(datediff(minute,xdate,mdate)/60.0,2) as varchar) end as 工作时长
,case when convert(varchar(8),xdate,108)=convert(varchar(8),mdate,108) then '异常' else '正常' end as 状态
from
(
select deptname,deptno,convert(varchar(10),cdate,120) as cdate,max(cdate) as mdate,min(cdate) as xdate
from #tb
group by deptname,deptno,convert(varchar(10),cdate,120)
)t
/*
部门 工号 日期 时间1 时间2 工作时长 状态
---------------------------------------------------------------------------------------
后勤A0012013-11-0208:00:2217:35:229.580000正常
后勤A0022013-11-0207:59:22----异常
生产A0032013-11-0207:50:0119:58:0112.130000正常
生产A0042013-11-0208:00:0020:00:0012.000000正常
生产A0052013-11-0220:30:00----异常
*/
