CODEWITHSUNDEEP
sqlsql-servercount
Dea Ananda
Dea Ananda

Reputation: 115

SQL Server : Count with condition

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

I need to SELECT(NIP,NAME,DEPARTMENT,LATECOME) and COUNT the records Count (where LATECOME > '00:00:01') > 2 in per month

and the output will be like this :

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

Because, Aria has LATECOME > 2 in per month and Belle only 1 LATECOME in per month

Upvotes: 0

Views: 69

Answers (2)

Deepak Mishra
Deepak Mishra

Reputation: 3193

select * from SummaryData
where name in
(select name from SummaryData where latecome > '00:00:01' group by name, year(statusin), month(statusin) having count(*)>2)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If I understand correctly, you can use window functions:

select t.*
from (select t.*,
             count(*) over (partition by nip, name, department) as cnt_late
      from t
      where latecome <> '00:00:00'
     ) t
where cnt_late > 2;

If you have multiple months in the data, then:

select t.*
from (select t.*,
             count(*) over (partition by nip, name, department, year(statusin), month(statusin)) as cnt_late
      from t
      where latecome <> '00:00:00'
     ) t
where cnt_late > 2;

Upvotes: 1

Related Questions