来自一个表的计数和来自另一个表的匹配计数

Question

我有一个工作订单表和客户满意度表。我需要从工单中计算不同的位置，我需要从客户满意度中计算匹配的位置。公共列为wo_number

select CONVERT(VARCHAR(7), DATEADD(MONTH, DATEDIFF(MONTH, 0, WO.wo_completionDate), 0), 111) AS [Period Date],
REPLACE(RIGHT(CONVERT(VARCHAR(11), DATEADD(MONTH, DATEDIFF(MONTH, 0, WO.wo_completionDate), 0), 106), 8), ' ', '-') AS Period,
count (distinct WO.fu_unitID),
count (distinct SR.fu_unitID)
from v_surveyResultRpt as SR
INNER JOIN v_workorder AS WO on SR.wo_number = WO.wo_number
where WO.wo_isClosed = '1' and WO.wo_charge = '1' and WO.rc_code = 'CD-HM'
and
DATEDIFF(MONTH,WO.wo_completionDate,getdate()) < 6
group by DATEADD(MONTH, DATEDIFF(MONTH, 0, WO.wo_completionDate), 0)
order by CONVERT(VARCHAR(7), DATEADD(MONTH, DATEDIFF(MONTH, 0, WO.wo_completionDate), 0), 111)

期望一个结果，如月YY，Total_location从客户满意度调查，从工作订单的总位置。最终，它将给我从总工作订单中响应的客户数量。

Answer 1

如果对你有帮助，请使用下面的示例代码。

select CAST(MONTH(WO.wo_completionDate) AS VARCHAR(2)) +'-'+ CAST(YEAR(WO.wo_completionDate) AS VARCHAR(4)),
count (distinct WO.fu_unitID),
count (distinct SR.fu_unitID)
from v_surveyResultRpt as SR
INNER JOIN v_workorder AS WO on SR.wo_number = WO.wo_number
where WO.wo_isClosed = '1' and WO.wo_charge = '1' and WO.rc_code = 'CD-HM'
and DATEDIFF(MONTH,WO.wo_completionDate,getdate()) < 6
group by MONTH(WO.wo_completionDate), YEAR(WO.wo_completionDate)
order by YEAR(WO.wo_completionDate), MONTH(WO.wo_completionDate)