Count Records Per Month With Condition

I have a table, let's call them SUMMARYDATA NIP NAME DEPARTMENT STATUSIN STATUSOUT LATECOME -------------------------------------------------------

Solution 1:

try the following:

;WITH cte AS 
(
    SELECTDISTINCT [NAME], DEPARTMENT, MONTH(STATUSIN) [MONTH], YEAR(STATUSIN) [YEAR], 
        SUM(CASEWHEN LATECOME ='00:00:00'THEN0ELSE1END) OVER(PARTITIONBY [NAME], DEPARTMENT, MONTH(STATUSIN), YEAR(STATUSIN) ORDERBY [NAME]) Total
    FROM SUMMARYDATA
)

SELECT DEPARTMENT, [MONTH], [YEAR], SUM(CASEWHEN TOTAL >2THEN1ELSE0END) LATECOME
FROM cte
GROUPBY DEPARTMENT, [MONTH], [YEAR] 

Please find the db<>fiddle here.

Solution 2:

Createtable #SUMMARYDATA(NIP varchar(10), NAME varchar(50),DEPARTMENT varchar(90),STATUSIN datetime, STATUSOUT datetime,LATECOME TIME(0))
Insertinto #SUMMARYDATA
Values('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')


Select s.DEPARTMENT,Q.[Month],Q.[Year], 
    CasewhenMax(LateCount) >2then1Else0Endas LateCome  
from #SUMMARYDATA s 
innerjoin 
(
 SELECT NAME, MONTH(StatusIn) as [Month] , YEAR(StatusIn) as [Year], 
 Sum(Casewhen LateCome >'00:00:00'then1Else0End) as LateCount 
 from #SUMMARYDATA
 groupby NAME,  MONTH(StatusIn), YEAR(StatusIn)
) Q 
ONMONTH(s.StatusIn) = Q.[Month] andYEAR(s.StatusIn) =Q.[Year] and s.NAME = q.NAME
groupby  s.DEPARTMENT,Q.[Month],Q.[Year]

--- Or make it a bit more elegant

;With sdCTE as
(
 SELECT NAME, DEPARTMENT, MONTH(StatusIn) as [Month] , YEAR(StatusIn) as [Year],  
 Casewhen LateCome >'00:00:00'then1Else0Endas LateCome 
 from #SUMMARYDATA
)
Select DEPARTMENT, [Month],[Year], 
    CasewhenMax(LateCount) >2then1Else0Endas LateCome  
from
(
    select Name, DEPARTMENT, [Month],[Year], Sum(LateCome) LateCount  
    from sdCTE
    groupby Name,DEPARTMENT, [Month],[Year]
) Q
Groupby DEPARTMENT, [Month],[Year]

Solution 3:

This is for the changed requirements

;With sdCTE as
(
 SELECT NAME, DEPARTMENT, MONTH(StatusIn) as [Month] , YEAR(StatusIn) as [Year],  
 Casewhen LateCome >'00:00:00'then1Else0Endas LateCome 
 from #SUMMARYDATA
)

Select DEPARTMENT, [Month],[Year], 
    Sum(Casewhen (LateCount) >2then1Else0End) as LateCome  
from
(
    select Name, DEPARTMENT, [Month],[Year], Sum(LateCome) LateCount  
    from sdCTE
    groupby Name,DEPARTMENT, [Month],[Year]
) Q
Groupby DEPARTMENT, [Month],[Year]