关于考勤的问题

[amazeyeli]

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

[开着拖拉机泡妞]

[Quote=引用 5 楼 的回复:]

回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 ……
[/Quote]

是的，然后你可以通过case when 来判断这几个时间了

[amazeyeli]

;WITH T
  AS
(
SELECT NAME,
  CASE
    WHEN TIME <=  '8:00'                  THEN '正常'
    WHEN TIME BETWEEN  '8:01' AND  '8:30' THEN '迟到'
    WHEN TIME BETWEEN  '8:31' AND '11:30' THEN '中途打卡'
    WHEN TIME BETWEEN '11:31' AND '11:59' THEN '早退' 
    WHEN TIME BETWEEN '12:00' AND '14:00' THEN '正常'
    WHEN TIME BETWEEN '14:01' AND '14:30' THEN '迟到'
    WHEN TIME BETWEEN '14:31' AND '17:30' THEN '中途打卡'
    WHEN TIME BETWEEN '17:31' AND '17:59' THEN '早退' 
    WHEN TIME >= '18:00'                  THEN '正常'
  ELSE '' END AS Notes
FROM TEST
)
SELECT A.NAME,A.正常,B.迟到,C.中途打卡,D.早退 
  FROM
(
SELECT NAME,COUNT(Notes) AS 正常 FROM T
  WHERE Notes = '正常' 
  GROUP BY NAME
) AS A
  LEFT JOIN 
(
SELECT NAME,COUNT(Notes) AS 迟到 FROM T
  WHERE Notes = '迟到' 
  GROUP BY NAME 
) AS B
  ON A.NAME = B.NAME   
  LEFT JOIN 
(
SELECT NAME,COUNT(Notes) AS 中途打卡 FROM T
  WHERE Notes = '中途打卡' 
  GROUP BY NAME 
) AS C
  ON A.NAME = C.NAME
  LEFT JOIN 
(
SELECT NAME,COUNT(Notes) AS 早退 FROM T
  WHERE Notes = '早退' 
  GROUP BY NAME 
) AS D
  ON A.NAME = D.NAME

[开着拖拉机泡妞]

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

[开着拖拉机泡妞]

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
*/


我觉得这么行列转换一下统计来的直观一点

[风一样的大叔]

以前貌似也有个帖子上有计算工作日的，不错介个

[Q315054403]

定规则，改设计。。非几个SQL之力

[amazeyeli]

回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 2012-06-01 07:55:00.0000000 12:05:00.0000000 14:05:00.0000000 18:05:00.0000000

[amazeyeli]

回3楼,这个不错,谢谢