以Case...When...Then...End分列输出

SELECT 日期, 姓名, (CASE 出勤状态 WHEN '白班' THEN '○' END) AS 白班, (CASE 出勤状态 WHEN '夜班' THEN '○' END) AS 夜班, (CASE 出勤状态 WHEN '休息' THEN '○' END) AS 休息, (CASE 出勤状态 WHEN '调休' THEN '○' END) AS 调休, (CASE 出勤状态 WHEN '病假' THEN '○' END) AS 病假, (CASE 出勤状态 WHEN '事假' THEN '○' END) AS 事假, (CASE 出勤状态 WHEN '迟到' THEN '○' END) AS 迟到, (CASE 出勤状态 WHEN '早退' THEN '○' END) AS 早退, (CASE 出勤状态 WHEN '旷工' THEN '○' END) AS 旷工, (CASE 出勤状态 WHEN '其他' THEN '○' END) AS 其他, 部门, 身份证号, id FROM dbo.人事_考勤表 WHERE (部门 = @部门) AND (日期 = @日期)





'按出勤状态统计某部门每天的出勤数据
SELECT 日期, 部门,
count(CASE 出勤状态 WHEN '白班' THEN '○' END) AS 白班,
count(CASE 出勤状态 WHEN '夜班' THEN '○' END) AS 夜班,
count(CASE 出勤状态 WHEN '休息' THEN '○' END) AS 休息,
count(CASE 出勤状态 WHEN '调休' THEN '○' END) AS 调休,
count(CASE 出勤状态 WHEN '病假' THEN '○' END) AS 病假,
count(CASE 出勤状态 WHEN '事假' THEN '○' END) AS 事假,
count(CASE 出勤状态 WHEN '迟到' THEN '○' END) AS 迟到,
count(CASE 出勤状态 WHEN '早退' THEN '○' END) AS 早退,
count(CASE 出勤状态 WHEN '旷工' THEN '○' END) AS 旷工,
count(CASE 出勤状态 WHEN '其他' THEN '○' END) AS 其他
FROM dbo.人事_考勤表
where 部门='设备工具科'
group by 日期,部门
order by 日期 desc

 

'按月统计各单位出勤状态
SELECT     STR(YEAR(日期)) + '-' + LTRIM(STR(MONTH(日期))) AS 日期, 部门, COUNT(CASE 出勤状态 WHEN '白班' THEN '○' END) AS 白班,
                      COUNT(CASE 出勤状态 WHEN '夜班' THEN '○' END) AS 夜班, COUNT(CASE 出勤状态 WHEN '休息' THEN '○' END) AS 休息,
                      COUNT(CASE 出勤状态 WHEN '调休' THEN '○' END) AS 调休, COUNT(CASE 出勤状态 WHEN '病假' THEN '○' END) AS 病假,
                      COUNT(CASE 出勤状态 WHEN '事假' THEN '○' END) AS 事假, COUNT(CASE 出勤状态 WHEN '迟到' THEN '○' END) AS 迟到,
                      COUNT(CASE 出勤状态 WHEN '早退' THEN '○' END) AS 早退, COUNT(CASE 出勤状态 WHEN '旷工' THEN '○' END) AS 旷工,
                      COUNT(CASE 出勤状态 WHEN '其他' THEN '○' END) AS 其他
FROM         dbo.人事_考勤表
WHERE     (部门 = @部门) AND (YEAR(日期) = YEAR(@日期))
GROUP BY STR(YEAR(日期)) + '-' + LTRIM(STR(MONTH(日期))), 部门
ORDER BY STR(YEAR(日期)) + '-' + LTRIM(STR(MONTH(日期))) DESC