我有一张桌子，我们打电话给他们吧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)) and COUNT记录Count (where LATECOME > '00:00:01') > 2每月。
因为，Aria 和 Brian 有LATECOME > 2每月，贝儿和子牙只有 1LATECOME，和克洛伊只有 2LATECOME每月。所以，在 BB 部门中只有 1 即 Aria，而在 CC 部门中为 0，因为 Chloe & Ziya<= 2 LATECOME.
这就是我的意思的示例输出：
DEPARTMENT MONTH YEAR LATECOME
-------------------------------------------
BB 01 2020 2
CC 01 2020 0
尝试以下操作：
;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]
请找到 dbfiddle.
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
