关于考勤的有关问题_Sql Server

关于考勤的问题

SQL code

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个休息日,--然后算出需要统计周期内的天数再减去休息日得到

------解决方案--------------------
我觉得正常就是在上班时间前打卡，下班时间到了后打卡。迟到就是上班后打卡，早退就是下班前打卡。这个可以用case when来判断。
------解决方案--------------------

SQL code

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之力
------解决方案--------------------
探讨

回2楼,如果忘记打卡,就会没有记录

把select '2012-6-1','12:10','aaa' union all删掉

结果就会这样:

name	date	上午上班	上午下班	下午上班	下午下班
aaa	2012-06-01	07:50:00.0000000	13:50:00.0000000	18:10:00.0000000	00:00:00.0000000
bbb	……