求教考勤表格SQL代码实现

求教考勤报表SQL代码实现

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' 00'union all select '生产','A005','2013-11-03 8:00: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       工作时长            状态 --------------------------------------------------------------------------------------- 后勤    A001    2013-11-02    08:00:22    17:35:22    9.580000    正常 后勤    A002    2013-11-02    07:59:22    --    --    异常 生产    A003    2013-11-02    07:50:01    19:58:01    12.130000    正常 生产    A004    2013-11-02    08:00:00    20:00:00    12.000000    正常 生产    A005    2013-11-02    20:30:00    8:00:00    11.5    异常   */ 

    说明:后勤部门上正常班 早上8点至下午17:30   生产部门分白班和夜班,白班早上8点至下午20:00,夜班是晚上20点至次日早上8点, 考勤
[解决办法]
数据库设计的问题前面有人说了，我就说点别的
这个你给出的测试表数据完整吗？比如A005会不会有超过2条记录？
暂时按照你的测试数据的话，下面的SQL是否可以实现你要的结果
另最后一条的状态是正常，我不知道你要求的结果中显示为异常是否还有另外的判断规则

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,min(convert(varchar(10),cdate,120)) as cdate,max(cdate) as mdate,min(cdate) as xdate
from #tb
group by deptname,deptno
)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:0008:00:0011.500000正常
*/

查看更多 下一篇