SQL Show value if a column not found

Posted 10 months ago by marufalom

I want to display a users monthly attendance. I got his attendance records but I want to display a value if an employee is not present.

SELECT USERID,MIN(CHECKTIME) in_time,MAX(CHECKTIME) out_time
FROM dbo.CHECKINOUT
WHERE CHECKTIME BETWEEN 
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND 
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
AND USERID = 2
GROUP BY CONVERT(varchar(8), CHECKTIME, 112),USERID

What I get

userid | in_time | out_time
2 | 2018-09-03 08:42:05.000 | 2018-09-03 17:28:39.000
2 | 2018-09-10 08:57:10.000 | 2018-09-10 17:26:38.000

What I want

userid | in_time | out_time
2 | 2018-09-03 08:42:05.000 | 2018-09-03 17:28:39.000
2 | 0 | 0
2 | 2018-09-10 08:57:10.000 | 2018-09-10 17:26:38.000

I want to show, If the data is not found then he is absent. It can be zero or anything.

Note: I am using Microsoft SQL Server

Please sign in or create an account to participate in this conversation.