每月记录有条件的记录 [英] Count records per month with condition
本文介绍了每月记录有条件的记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一张桌子，我们称它们为 SUMMARYDATA
I have a table, let's call them 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（部门，月份，年份，计数（最新））和 COUNT 条记录 Count（其中LATECOME> ‘00：00：01’）>每月2 。
因为Aria& Brian的 LATECOME> Belle&每月2 Ziya每月只有1个 LATECOME ，而Chloe每月只有2个<$ c $ lateCOME 。因此，在部门BB中，只有1即Aria，而部门CC是0，因为Chloe& Ziya < = 2 LATECOME 。
Because, Aria & Brian has LATECOME > 2 in per month, Belle & Ziya only 1 LATECOME, and Chloe Only 2 LATECOME in per month. So, in Department BB just 1 that is Aria, and Department CC is 0, because Chloe & Ziya <= 2 LATECOME.
这是我的意思是示例输出：
And this is the sample output that I mean:
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]
请找到db<>小提琴 此处 。
Please find the db<>fiddle here.
这篇关于每月记录有条件的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
