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

Question

我有一张表，叫他们 SUMMARYDATA

NIP  NAME   DEPARTMENT       STATUSIN           STATUSOUT               LATECOME
------------------------------------------------------------------------------------------------
A1   ARIA   BB         2020-01-21 08:06:23  2020-01-21 11:58:36         00:06:23             
A1   ARIA   BB         2020-01-22 07:34:27  2020-01-22 17:19:47         00:00:00               
A1   ARIA   BB         2020-01-23 08:30:00  2020-01-23 11:00:00         00:30:00
A1   ARIA   BB         2020-01-24 08:05:00  2020-01-24 10:30:00         00:05:00
A2   BELLE  BB         2020-01-21 07:06:20  2020-01-21 13:58:31         00:00:00             
A2   BELLE  BB         2020-01-22 07:34:27  2020-01-22 17:19:47         00:00:00               
A2   BELLE  BB         2020-01-23 07:06:00  2020-01-23 10:30:00         00:00:00
A2   BELLE  BB         2020-01-24 09:06:00  2020-01-23 10:30:00         02:06:00
A3   CHLOE  CC         2020-01-21 07:06:23  2020-01-21 11:55:30         00:00:00             
A3   CHLOE  CC         2020-01-22 07:34:27  2020-01-22 17:00:44         00:00:00               
A3   CHLOE  CC         2020-01-23 08:37:00  2020-01-23 11:13:00         00:37:00
A3   CHLOE  CC         2020-01-24 08:09:00  2020-01-24 10:22:00         00:09:00
A4   ZIYA   CC         2020-01-21 07:06:20  2020-01-21 13:58:31         00:00:00             
A4   ZIYA   CC         2020-01-22 07:34:27  2020-01-22 17:19:47         00:00:00               
A4   ZIYA   CC         2020-01-23 06:06:00  2020-01-23 11:30:00         00:00:00
A4   ZIYA   CC         2020-01-24 09:06:00  2020-01-23 15:30:00         02:06:00
A5   BRIAN   BB         2020-01-21 08:06:23  2020-01-21 11:58:36         00:06:23             
A5   BRIAN   BB         2020-01-22 07:34:27  2020-01-22 17:19:47         00:00:00               
A5   BRIAN   BB         2020-01-23 08:30:00  2020-01-23 11:00:00         00:30:00
A5   BRIAN   BB         2020-01-24 08:05:00  2020-01-24 10:30:00         00:05:00

我需要SELECT(DEPARTMENT,MONTH,YEARS,COUNT(LATECOME))和每月COUNT的记录Count (where LATECOME > '00:00:01') > 2。

因为Aria和BrianLATECOME > 2每月收入只有Belle＆Ziya1个LATECOME，而ChloeLATECOME每月收入只有2个。因此，在部门BB中，只有1即Aria，而部门CC为0，因为Chloe＆Ziya<= 2 LATECOME。

这是我的意思是示例输出：

DEPARTMENT     MONTH    YEAR       LATECOME
-------------------------------------------
  BB            01      2020           2              
  CC            01      2020           0

Answer 1

请尝试以下操作：

;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 。