Click here to Skip to main content
15,846,346 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
DECLARE @attendance_date datetime
set @attendance_date = '2014-02-19'

select TM.TM_UserID, TM.FullName, @attendance_date from TM_User TM
where TM.is_active=1 and TM.TM_UserID not in
(SELECT EM2.EmployeeID from Employee_Attendance EM2 WHERE convert(varchar(15),EM2.CheckTime,105) = convert(varchar(15),'19-02-2014',105))
Posted
Updated 25-Feb-14 3:14am
v3
Comments
Peter Leow 25-Feb-14 9:21am    
Why did you changed your question? My solution 3 would have answered your original question.

don't need Group by clause,if you have any aggregate function then you need Group by Clause

SQL
select  TM.UserId,TM.FullName,EM.checktime from TM_User TM left join Employee_Attendance EM
on TM.TM_UserID=EM.EmployeeID
where  TM.TM_UserID not in (select EmployeeID from Employee_Attendance )
and TM.Is_Active=1 and EM.Checktime ='2012-11-21 00:00:00.000'
 
Share this answer
 
v2
Comments
Member 10501509 20-Feb-14 7:13am    
actually i am finding absentees for particular date
King Fisher 20-Feb-14 7:16am    
not clear..
For a particular date, regardless of the time, try:

SQL
select  TM.UserId,TM.FullName,EM.checktime from TM_User TM left join Employee_Attendance EM
on TM.TM_UserID=EM.EmployeeID
where  TM.TM_UserID not in (select EmployeeID from Employee_Attendance )
and TM.Is_Active=1 and DATEDIFF(d, EM.Checktime, '2012-11-21 00:00:00.000') = 0
 
Share this answer
 
Comments
Member 10501509 20-Feb-14 7:12am    
i am not getting any records for that date
Member 10501509 20-Feb-14 7:12am    
actually i am finding absentees for particular date
Jorge J. Martins 20-Feb-14 7:18am    
Then your issue is with the data, not the query. It's better then.
Please be a sport and mark this as answered or delete it, ok?
You should put the checktime date in the where clause of the sub query in order to check for non existence of employees on that date.
Assuming the checktime is of datetime type, try this:
SQL
DECLARE @attendance_date datetime
set @attendance_date  = '2014-02-20'
select  TM.TM_UserId, TM.FullName, @attendance_date from TM_User TM left join Employee_Attendance EM
on TM.TM_UserID=EM.EmployeeID
where  TM.Is_Active=1 AND NOT EXISTS
(SELECT * from Employee_Attendance EM2 WHERE EM2.CheckTime=@attendance_date AND
 EM2.EmployeeID = TM.TM_UserID )
 
Share this answer
 
v2
Comments
Member 10501509 25-Feb-14 7:23am    
from this i will get absentees list for then i need absentees date also
Peter Leow 25-Feb-14 7:49am    
It should show as @attendance_date. Have you tried it?
Member 10501509 25-Feb-14 8:14am    
set @attendance_date in between range
Peter Leow 25-Feb-14 9:20am    
You have just changed your original question. Anyway, you should be able to solve your modified question by extending solution 3. That is your homework.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900