CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Count should return only if both times are present

I want to calculate the number of days employee was present. If he/she doesn't have timeout then it should not consider their present.

CREATE TABLE Attendance(
[EmpCode] INT
,[TimeIn] DATETIME
,[TimeOut] DATETIME
)
INSERT INTO Attendance VALUES (12,      '2018-08-01 09:00:00.000',      '2018-08-01 17:36:00.000');
INSERT INTO Attendance VALUES (12,      '2018-08-02 09:00:00.000',      NULL);
INSERT INTO Attendance VALUES (12,      '2018-08-03 09:25:00.000',      '2018-08-03 16:56:00.000');
INSERT INTO Attendance VALUES (12,      '2018-08-04 09:13:00.000',      NULL);
INSERT INTO Attendance VALUES (12,      '2018-08-06 09:00:00.000',      '2018-08-07 18:15:00.000');
INSERT INTO Attendance VALUES (12,      '2018-08-07 09:27:00.000',      NULL);

My query is returning 6 days but it should return 3 days as only 3 days have both time in and time out.

SELECT 
COUNT(CAST(COALESCE([TimeIn], [TimeOut]) AS DATE))
FROM [dbo].[Attendance]
WHERE
CAST(COALESCE([TimeIn], [TimeOut]) AS DATE) BETWEEN '2018-08-01' AND '2018-08-07'

Upvotes: 0

Views: 36

Answers (1)

Squirrel
Squirrel

Reputation: 24783

just add a WHERE condition checking for TimeOut is not null

SELECT 
    COUNT(CAST(COALESCE([TimeIn], [TimeOut]) AS DATE))
FROM    
    [dbo].[Attendance]
WHERE   
    [TimeOut] is not null
and 
    CAST(COALESCE([TimeIn], [TimeOut]) AS DATE) BETWEEN '2018-08-01' AND '2018-08-07'

Upvotes: 1

Related Questions