求教考勤报表SQL代码实现

[shixin333]

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点,

[shixin333]

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' 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 异常 */

[唐诗三百首]

LZ的查询SQL不是已经实现了吗?

[shixin333]

生产 A005 2013-11-02 20:30:00 8:00:00 11.5 异常 这一行是手工改上去的,这段代码实现不了,需要大吓帮助

[Q315054403]

这是设计问题，非SQL问题 用复杂的SQL去查询，一定是没设计过考勤方案 还有迟到、早退及次数统计、请假、调班、不同班次等处理

[CAINIAO123]

受教了，谢谢

[iailum]

数据库设计的问题前面有人说了，我就说点别的 这个你给出的测试表数据完整吗？比如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	工作时长	状态
后勤	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	08:00:00	11.500000	正常
*/

[shixin333]

@iailum A005 也可能会有多条数据 我的问题是事先不能提供排班表,如果没有排班表,好像很难实现 有没有好一点的考勤软件,求推荐

[treemo]

[zbdzjx]

考勤的这个问题，我也经常奇怪，居然有些软件说不用排班，通过打卡能自动算出排班。 后来看过一家的代码，他们是有默认的班次，就是这个人所在部门或个人有默认班次，也可以手工排班。 在计算排班时，先看有没有手工排班，没有，就按默认的班次处理。 但我之前的公司是很难这样处理的，因为是半个月调一次班，而且一个部门当中，有的调班，有的不调。所以，后来都是要手工排班，不然没法计算。 例如：一个人在11/6日打了两次卡，一次是8：00，一次是20：00，谁知道他上的是白班还是夜班？？！！！

[tsp3ng]

例如：一个人在11/6日打了两次卡，一次是8：00，一次是20：00，谁知道他上的是白班还是夜班？？！！！ -> 可以结合前一天的考勤来处理的, 不是凭一天的考勤卡能判断的. 如果08:00是前一天晚班下班卡,那么20:00就是11/6的是晚班上班卡. 如果前一天缺少上班卡20:00, 那么前一天的考勤会异常,需要签卡处理. 不用排班是可以实现的.

[tsp3ng]

考勤问题千言万语估计都说不清楚,每个企业都不一样. 要解决这个问题,必须要从每个时间点,每个考勤对象去分析. 仅仅考虑迟到的情况.都有N个规则. 例如 考勤系统中某员工A（技术部的工程师, 属于月薪人员)在2008-8-8日08:05 才来上班，本来上班时间08:00, 那么这个员工算不算迟到 第1步检查是否对该员工2008-8-8这一天作了特殊规则，如果这一天奥运会开幕，可以不计算迟到，那么该员工不算迟到，如果没有特殊规定 第2步检查是否对该员工有特别的规定，例如不计迟到，或者允许迟到10分钟，那么该员工就应该不算迟到, 如果没有特殊规定 第3步检查技术部的工程师是否有特殊规定，例如不计迟到，或者允许迟到10分钟，那么该员工就应该不算迟到, 如果没有特殊规定 第4步检查技术部的月薪是否有特殊规定，例如不计迟到，或者允许迟到10分钟，那么该员工就应该不算迟到, 如果没有特殊规定 第5步检查工程师是否有职位规定，例如不计迟到，或者允许迟到10分钟，那么该员工就应该不算迟到, 如果没有特殊规定 第6步检查技术部是否存在部门规定, 如果没有, 则要往上级部门一级一级找下去, 直到找到或者到顶级部门， 第7步检查月薪人员是否存在规定，例如不计迟到，或者允许迟到10分钟，那么该员工就应该不算迟到, 如果没有特殊规定 第8步按整个公司的制度执行, 如果整个公司允许迟到5分钟，则不算迟到，否则按迟到处理。 第9步如果子公司没有制度执行, 则需要找到集团的制度执行. 如果要做一个全完通用(指不做任何修改,适应所有的企业)的考勤系统, 难,难,难. 觉得比起MRP运行过程还难,规则千奇百怪