c# - SQL Server Pivot - 计算部门明智的每月出勤率
问题描述
我正在查询按部门获取每月出勤总数。即它将根据考勤日志表显示每个部门的当前员工,其中check_type = 'C-IN'
- 表格1:
checkin_out_log
- 列:
emp_id
,check_time
,check_type
(C-IN, C-OUT)
- 列:
- 表 2:
department
- 表3:
employee
预期输出为
Department | Total Staff | Date 1 | Date 2 | Date 3|......
finance | 60 | 50 | 55 | 48 |.....
这是我的查询,目前正在返回每个部门的员工总数,
SELECT
*
FROM
(SELECT
d.id, d.name AS Department, T.DateToCheck,
COUNT(e.id) AS staff
FROM
employee e
CROSS JOIN
(SELECT
CAST(DATEADD(DAY, number, @DateFrom) AS DATE)
FROM
master..spt_values
WHERE
type = 'P'
AND DATEADD(DAY, number, @DateFrom) <= @DateTo ) T (DateToCheck)
LEFT JOIN
department d ON d.id = e.department
WHERE
d.id = 7
GROUP BY
d.id, d.name, T.DateToCheck) T
PIVOT
(MAX(staff)
FOR DateToCheck IN ([2018-09-01],[2018-09-02],[2018-09-03],[2018-09-04],[2018-09-05],[2018-09-06],[2018-09-07],[2018-09-08],[2018-09-09],[2018-09-10],
[2018-09-11],[2018-09-12],[2018-09-13],[2018-09-14],[2018-09-15],[2018-09-16],[2018-09-17],[2018-09-18],[2018-09-19],[2018-09-20],[2018-09-21],[2018-09-22],[2018-09-23],
[2018-09-24],[2018-09-25],[2018-09-26],[2018-09-27],[2018-09-28],[2018-09-29],[2018-09-30])) P
;
解决方案
推荐阅读
- r - How to save the password in script R in package ‘encryptr’?
- sharedpreferences - Using SharedPreferences or File on MVVM
- ruby - How can I remove non-printable invisible characters from string?
- java - 将日历添加到数据库后更改日期
- javascript - 识别 AJAX 请求
- reactjs - 无法从“node_modules/react-redux/lib/index.js”解析“./components/connect”
- apache-kafka - Kafka 通过 Hyperledger Fabric Orderer 连接提供无效的接收大小
- python - 在 csv 字典/嵌套 csv 字典的帮助下使用 Python 编写 CSV 文件
- vue.js - 在 vuechart.js HorizontalBar 中将默认值设置为 0
- protractor - 将量角器 chrome 驱动程序更新到 74