关于考勤的问题
IF OBJECT_ID('TEST') IS NOT NULL DROP TABLE TEST; CREATE TABLE TEST( DATE DATE, TIME TIME, NAME NVARCHAR(10));INSERT INTO TEST select '2012-6-1','7:50','aaa' union all select '2012-6-1','7:55','bbb' union all select '2012-6-1','12:10','aaa' union all select '2012-6-1','12:05','bbb' union all select '2012-6-1','13:50','aaa' union all select '2012-6-1','14:05','bbb' union all select '2012-6-1','18:10','aaa' union all select '2012-6-1','18:05','bbb'; SELECT A.NAME,A.Normal,B.Later,C.Early, Neglect = (((DATEDIFF(DAY,'2012-6-1','2012-7-1')-8)*4)-ISNULL(A.Normal,0)-ISNULL(B.Later,0)-ISNULL(C.Early,0))*0.25--旷工 FROM (( SELECT NAME,COUNT([TIME]) AS Normal--正常 FROM TEST WHERE [TIME] <= '8:00' OR [TIME] BETWEEN '12:00' AND '14:00' OR [TIME] >= '18:00' GROUP BY NAME ) AS A LEFT JOIN( SELECT NAME,COUNT(TIME) AS Later--迟到 FROM TEST WHERE TIME BETWEEN '8:01' AND '8:30' OR TIME BETWEEN '14:01' AND '14:30' GROUP BY NAME ) AS B ON A.NAME = B.NAME LEFT JOIN( SELECT NAME,COUNT(TIME) AS Early--早退 FROM TEST WHERE TIME BETWEEN '8:31' AND '11:59' OR TIME BETWEEN '14:31' AND '17:59' GROUP BY NAME ) AS C ON A.NAME = C.NAME);--问题1:将 BETWEEN '8:31' AND '11:59' 划分为早退,并不合理--比如某同事,10:00才来打卡,12:00又打了下班卡,并不属于早退--该问题应如何解决?--问题2:一天必须打4次卡,少打一次就按旷工0.25天计算,此方法是否合理?--问题3:统计周期内的工作日如何得到? 我是查万年历:6月份8个休息日,--然后算出需要统计周期内的天数再减去休息日得到 IF OBJECT_ID('TEST') IS NOT NULL DROP TABLE TEST; CREATE TABLE TEST( [DATE] DATE, [TIME] TIME, NAME NVARCHAR(10));INSERT INTO TEST select '2012-6-1','7:50','aaa' union all select '2012-6-1','7:55','bbb' union all select '2012-6-1','12:10','aaa' union all select '2012-6-1','12:05','bbb' union all select '2012-6-1','13:50','aaa' union all select '2012-6-1','14:05','bbb' union all select '2012-6-1','18:10','aaa' union all select '2012-6-1','18:05','bbb';with tas(select *,px=ROW_NUMBER()over(partition by NAME,[DATE] order by [DATE],[TIME])from test)select name,[date],MAX(case when px=1 then [TIME] else '' end) as 上午上班,MAX(case when px=2 then [TIME] else '' end) as 上午下班,MAX(case when px=3 then [TIME] else '' end) as 下午上班,MAX(case when px=4 then [TIME] else '' end) as 下午下班from tgroup by name,[date]/*name date 上午上班 上午下班 下午上班 下午下班----------------------------------------------------------------aaa 2012-06-01 07:50:00.0000000 12:10:00.0000000 13:50:00.0000000 18:10:00.0000000bbb 2012-06-01 07:55:00.0000000 12:05:00.0000000 14:05:00.0000000 18:05:00.0000000*/我觉得这么行列转换一下统计来的直观一点
[解决办法]
以前貌似也有个帖子上有计算工作日的，不错介个
[解决办法]
定规则，改设计。。非几个SQL之力
[解决办法]