使用条件SQL每月统计摘要记录

Question

我有一张表，我们称它们为桌子 SUMMARYDATA

NIP  NAME   DEPARTMENT       STATUSIN           STATUSOUT             TOTALLOSTTIME 
------------------------------------------------------------------------------------------------
A1   ARIA   BB         2020-01-21 08:06:23  2020-01-21 11:58:36         00:23:15             
A2   CHLOE  BB         2020-01-21 07:34:27  2020-01-21 17:19:47         01:19:19               
A1   ARIA   BB         2020-01-22 08:00:00  2020-01-22 11:00:00         01:10:00
A2   CHLOE  BB         2020-01-22 08:05:00  2020-01-21 10:30:00         00:20:00
A11  BELLE  CC         2020-01-21 09:06:20  2020-01-21 13:58:31         00:25:10             
A21  ZIYA   CC         2020-01-21 08:34:27  2020-01-21 17:19:47         02:29:39               
A11  BELLE  CC         2020-01-22 07:06:00  2020-01-22 10:30:00         01:11:00
A21  ZIYA   CC         2020-01-22 09:05:00  2020-01-21 11:40:00         00:20:00

我需要每月COUNT的记录where sum(TOTALLOSTIME) group by nip,name,department > 02:00:00并更新为table REPORTDATA，这是我的意思是一个示例输出：

DEPARTMENT     MONTH    YEAR   RTOTALLOSTTIME 
------------------------------------------------------------------------------------------------
  BB            01      2020           0              
  CC            01      2020           1

在数据类型列TOTALLOSTTIMEISnvarchar

到目前为止，这是我尝试过的：

select  MONTH(STATUSIN), YEAR(STATUSIN),NIP,NAME,DEPARTMENT,convert(varchar,dateadd(second,sum(datediff(second,'00:00:00',cast(TOTALLOSTTIME as time))),0),108) as sum_lost_time
from SUMMARYDATA b group by MONTH(STATUSIN), YEAR(STATUSIN),NIP,NAME,DEPARTMENT

该查询仅TOTALLOSTTIME在每个员工和每个月显示一次。

Answer 1

尝试以下

select x.department
       ,x.month
       ,x.year     
       ,count(case when x.sum_lost_time >'02:00:00' then NAME end) as RTOTALLOSTTIME 
 from (select MONTH(STATUSIN)  as [month]
               ,YEAR(STATUSIN) as [year]
               ,NIP
               ,NAME
               ,DEPARTMENT         
               ,convert(varchar,dateadd(second,sum(datediff(second,'00:00:00',cast(TOTALLOSTTIME as time))),0),108) as sum_lost_time
          from SUMMARYDATA b 
      group by MONTH(STATUSIN)
               ,YEAR(STATUSIN)
               ,NIP
               ,NAME
               ,DEPARTMENT
        )x
group by  x.department
         ,x.month
         ,x.year