每月对有条件的记录进行计数

请尝试以下 *** 作：

;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) LATECOMEFROM cteGROUP BY DEPARTMENT, [MONTH], [YEAR]

请在
此处
找到db <> fiddle 。