请尝试以下操作:
;WITH cte AS
(
SELECT DISTINCT [NAME], DEPARTMENT, MONTH(STATUSIN) [MONTH], YEAR(STATUSIN) [YEAR],
SUM(CASE WHEN LATECOME = '00:00:00' THEN 0 ELSE 1 END) OVER(PARTITION BY [NAME], DEPARTMENT, MONTH(STATUSIN), YEAR(STATUSIN) ORDER BY [NAME]) Total
FROM SUMMARYDATA
)
SELECT DEPARTMENT, [MONTH], [YEAR], SUM(CASE WHEN TOTAL > 2 THEN 1 ELSE 0 END) LATECOME
FROM cte
GROUP BY DEPARTMENT, [MONTH], [YEAR]
请在 找到db <> fiddle 。