I don't know what's ur database structure is, But I will explain you the solution in very simple example
Details:
I am using xyz device to record employees in-time. My database has two tables Access_Logs and usermaster
Access_Logs table stores information from device about userid and indatetime
usermaster table stores all user information(Employee information)
Now I want a list of employee who are absent on 2014-12-20
See below query and modify your query to get the result
Database Structure:
DECLARE @Access_Logs AS TABLE (UserId NVARCHAR(10),Indate DATETIME)
DECLARE @usermaster AS TABLE (UserId NVARCHAR(10),Username NVARCHAR(MAX))
Data:
INSERT INTO @usermaster
SELECT 'U01','John' UNION
SELECT 'U02','Jessy' UNION
SELECT 'U03','Michel' UNION
SELECT 'U04','Robert' UNION
SELECT 'U05','Dolly'
INSERT INTO @Access_Logs
SELECT 'U01','2014-12-20 21:24:20.200' UNION
SELECT 'U03','2014-12-20 21:24:20.200'UNION
SELECT 'U04','2013-12-20 21:24:20.200'
Main Query to get the absent employee on the date:
SELECT um.* FROM @usermaster um LEFT JOIN @Access_Logs al ON um.UserId=al.UserId and al.Indate BETWEEN '2014-12-20 00:00:00' AND '2014-12-20 23:59:59'
WHERE al.userid is null
Hope this helps....:)