我能找到每月事件的独特解决方案吗

Question

我创建了一个脚本，该脚本显示了使用以下脚本每月在一个团队中有多少独特的个人解决了呼叫。

有没有一种方法可以找出这些人，但仍然根据我已经完成的剧本显示每个团队的月度数据？

SELECT CAST(DATEPART(year, stat_datetimeresolved) as varchar(4)) + '-' + RIGHT('0' + CAST(DATEPART(month, stat_datetimeresolved) as varchar(2)), 2) as MonthResolved, ResolvedByTeam, COUNT(DISTINCT resolvedby) as ResolvedByCnt
FROM [dbo].[Incident]
WHERE stat_datetimeresolved >= '20170401'
GROUP BY CAST(DATEPART(year, stat_datetimeresolved) as varchar(4)) + '-' + RIGHT('0' + CAST(DATEPART(month, stat_datetimeresolved) as varchar(2)), 2), ResolvedByTeam
ORDER BY MonthResolved asc, ResolvedByTeam

Answer 1

您有两个选择

1. 展开结果并为MonthResolved、ResolvedByTeam和resolvedby的每个唯一组合返回单独的行。这将返回比原始查询更多的行，因为在一个月/团队的组合中，您将获得与解析器一样多的行
2. 向查询中再添加一列，在其中返回一个包含解析器名称的列表(例如逗号分隔(。这可以通过STRING_AGG函数轻松完成，但它仅在SQL Server 2017及更新版本中可用。对于2008，您的选择是使用For XML子句执行一些丑陋的转换

以下是两个查询，用于实现选项1和选项2:

;with cte1 as (
SELECT
CAST(DATEPART(year, stat_datetimeresolved) as varchar(4)) + '-' + RIGHT('0' + CAST(DATEPART(month, stat_datetimeresolved) as varchar(2)), 2) as MonthResolved
, ResolvedByTeam
, resolvedby
FROM [dbo].[Incident]
WHERE stat_datetimeresolved >= '20170401'
GROUP BY CAST(DATEPART(year, stat_datetimeresolved) as varchar(4)) + '-' + RIGHT('0' + CAST(DATEPART(month, stat_datetimeresolved) as varchar(2)), 2), ResolvedByTeam, resolvedby
)
select
MonthResolved
, ResolvedByTeam
, (select count(distinct resolvedby) as Name
from [dbo].[Incident] i 
where CAST(DATEPART(year, stat_datetimeresolved) as varchar(4)) + '-' + RIGHT('0' + CAST(DATEPART(month, stat_datetimeresolved) as varchar(2)), 2) = cte1.MonthResolved
and i.ResolvedByTeam = cte1.ResolvedByTeam)
, resolvedby
from cte1
ORDER BY MonthResolved asc, ResolvedByTeam, resolvedby
;with cte2 as (
SELECT
CAST(DATEPART(year, stat_datetimeresolved) as varchar(4)) + '-' + RIGHT('0' + CAST(DATEPART(month, stat_datetimeresolved) as varchar(2)), 2) as MonthResolved
, ResolvedByTeam
, COUNT(DISTINCT resolvedby) as ResolvedByCnt
FROM [dbo].[Incident]
WHERE stat_datetimeresolved >= '20170401'
GROUP BY CAST(DATEPART(year, stat_datetimeresolved) as varchar(4)) + '-' + RIGHT('0' + CAST(DATEPART(month, stat_datetimeresolved) as varchar(2)), 2), ResolvedByTeam
)
select *
from cte2
cross apply (select (
select distinct resolvedby + ', ' as [text()]
from [dbo].[Incident] 
where ResolvedByTeam = cte2.ResolvedByTeam
and CAST(DATEPART(year, stat_datetimeresolved) as varchar(4)) + '-' + RIGHT('0' + CAST(DATEPART(month, stat_datetimeresolved) as varchar(2)), 2) = cte2.MonthResolved
for xml path ('')) as Names) t
ORDER BY MonthResolved asc, ResolvedByTeam