By Using Below Query I Am Fetching Absentees And Their Absentee Date Same Thing I Want To Do For Between Dates How To Do That

Question

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 20-Feb-14 0:46am

Member 10501509

Updated 25-Feb-14 2:14am

v3

Add a Solution

Comments

Peter Leow 25-Feb-14 9:21am

Why did you changed your question? My solution 3 would have answered your original question.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

King Fisher · Answer 1 · 2014-02-20T00:51:00

Solution 1

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'

Posted 20-Feb-14 0:51am

King Fisher

Updated 20-Feb-14 0:53am

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

Jorge J. Martins · Answer 2 · 2014-02-20T00:59:00

Solution 2

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

Posted 20-Feb-14 0:59am

Jorge J. Martins

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?

Peter Leow · Answer 3 · 2014-02-20T03:09:00

Solution 3

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 )

Posted 20-Feb-14 3:09am

Peter Leow

Updated 20-Feb-14 3:10am

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.