我有表(UserReport)，其中列出了缺少公司必需的AD属性的用户.在该表中，我有ReportDate列(这是我运行的每周报告的日期).我有一个不同的位置字段，名为Site Rolls Up to Department.我想给每个网站的最近3个报告日期和每个网站的计数历史支点.下面是我现在得到的.
SQL查询范围
SELECT division, site, reportdate, COUNT(reportdate) as CountUsers
FROM UserReport
WHERE division = 'US South'
GROUP BY division, site, reportdate
结果
| Division | Site | ReportDate | CountUsers |
|---|---|---|---|
| US South | Texas | 2023-05-24 | 1 |
| US South | Florida | 2023-05-24 | 1 |
| US South | Ohio | 2023-05-24 | 1 |
| US South | Ohio | 2023-05-26 | 1 |
| US South | Kansas | 2023-05-19 | 5 |
| US South | Idaho | 2023-05-24 | 1 |
| US South | Utah | 2023-05-24 | 1 |
| US South | Georgia | 2023-05-24 | 1 |
| US South | Georgia | 2023-05-26 | 1 |
结果 that I want
| Division | Site | 2023-05-19 | 2023-05-24 | 2023-05-26 |
|---|---|---|---|---|
| US South | Texas | 0 | 1 | 0 |
| US South | Florida | 0 | 1 | 0 |
| US South | Ohio | 0 | 1 | 1 |
| US South | Kansas | 5 | 0 | 0 |
| US South | Idaho | 0 | 1 | 0 |
| US South | Utah | 0 | 1 | 0 |
| US South | Georgia | 0 | 1 | 1 |