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 t
as(
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 t
group by name,[date]

/*
name    date    上午上班    上午下班    下午上班    下午下班
----------------------------------------------------------------
aaa    2012-06-01    07:50:00.0000000    12:10:00.0000000    13:50:00.0000000    18:10:00.0000000
bbb    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	……