[RESOLVED] How to get all employees, who have NOT had attendance entries?

[rack]

I'm trying to figure out how to get all employee names, that don't have a record in tblAttendance, for a specific period?

I can't think of a way to say, where data not found, or where entry not found?

I'm guessing i'll have to do a Union to get all employee names. Then maybe do a count, or something. Combine the two and if the employee name has 0 for the count, they had perfect attendace?

Can I get a few pointers in the correct direction, I'd like to play and see if I can figure it out. I'll post once I give up on trying to see if someone has an answer.

Please don't just post the solution yet. =)

[brucevde]

You won't need to use a Union. Check out the IN operator of the SQL language for whichever database you are using.

[mendhak]

Search for "EXISTS"

[rack]

I think I figured it out. Please reply with if I have made any errors, or if there is a better way to do this, to shorten up the queries saved, and the run time.

Also, I need to exclude 2 items from the count.
If Exception="Approved Unscheduled" or Exception="Deleted".
I'm not sure how to add that, maybe in the count function?

I did a Union query, followed by a sum query.

PA stands for Perfect Attendance

Q3_PA

SELECT tblAgents.AgentName, 0 AS Occurances
FROM tblAgents
WHERE tblagents.active=true;
UNION ALL SELECT tblAgents.AgentName, Count(tblAttendance.AgentName) AS Occurances
FROM tblAgents INNER JOIN tblAttendance ON tblAgents.AgentName = tblAttendance.AgentName
WHERE (tblAttendance.Date)>#2/1/2008# and tblagents.active=true
GROUP BY tblAgents.AgentName;

Q4_PA

SELECT q3_PA.AgentName, Sum(q3_PA.Occurances) AS TotalOccurances
FROM q3_PA
GROUP BY q3_PA.AgentName
ORDER BY Sum(q3_PA.Occurances);

[rack]

While I was typing two posts came through. I'll look up IN and Exists.

[rack]

WOOT!!!!

Alright thank you both so much.

I think I got it working with NO EXISTS. It returned 261 employees, the same amount as the union query I built, so either they both work, or they are both wrong ;p

SELECT tblAgents.AgentName
FROM tblAgents
WHERE tblagents.active=true AND NOT EXISTS  (SELECT tblattendance.agentname FROM tblattendance WHERE tblagents.Agentname=tblAttendance.AgentName AND tblattendance.date>#02/01/2008#);

Please let me know if I understood the EXISTS correctly, and if I have made any mistakes =)

Thank you thank you again!

[mendhak]

Try looking at the execution plan to see which one is more efficient.

[rack]

Only need a list of employees, Don't need to know how many times they were absent. The No Exists works perfect, and I don't end up with multipule queries.

For the last bit, how do I make sure that if an entry is in the attendence table, but the Exception field ="Approved Unscheduled" or "Deleted" it shows there name as Perfect Attendance?

We use Approved Unscheduled for FMLA (Famly Medical Leave Act)
We use Deleted for an entry that was explain, fixed, or approved later.

So employee's with these two items should still get credit for having perfect attendance.

I tried to put the tblattendance.execption="Approved Unscheduled" in a few places, but it didn't work.

[anhn]

"NOT EXIST" can do the job but I think use "NOT IN" is more efficient because:
With "NOT EXIST", if tblAgents has 1000 records then the sub query need to be executed 1000 times.
With "NOT IN", the sub query need to be executed only once:

SELECT tblAgents.AgentName 
FROM tblAgents 
WHERE tblAgents.Active = True And tblAgents.AgentName NOT IN
(SELECT DISTINCT tblAttendance.AgentName FROM tblAttendance
WHERE tblAttendance.Date > #02/01/2008#);

Another method is :

SELECT DISTINCT tblAgents.AgentName 
FROM tblAgents LEFT JOIN tblAttendance 
ON tblAgents.AgentName = tblAttendance.AgentName
WHERE tblAgents.Active = True And 
(tblAttendance.AgentName Is Null OR tblAttendance.Date <= #02/01/2008#);

But use "NOT IN" may be better.

[brucevde]

I tried to put the tblattendance.execption="Approved Unscheduled" in a few places, but it didn't work.

In this case you need to check if the Exception field is Not equal to one or more values.

SELECT tblAgents.AgentName
FROM tblAgents
WHERE tblagents.active=true AND NOT EXISTS  
     (SELECT tblattendance.agentname 
      FROM tblattendance 
      WHERE tblagents.Agentname=tblAttendance.AgentName AND
                tblattendance.date>#02/01/2008# And 
                tblAttendance.Exception Not In ('Approved Unscheduled','Deleted')
     )

[rack]

I did a few extra seperate queries to analyze the data. I guess it was working. The records only went up by 2, so I thought perhaps I had messed up. When I went through and counted myself, I got the same number, and the same agents. Everything is working. Thank you all for your input. I am going to attempt to give you all positive feedback, but for some reason, It's very picky about how often you can give someoen positive feedback.

[mendhak]

I have to disagree with anhn. "IN" will perform a full table scan, "EXIST" can use any indices on the table columns in the sub query. Surely the EXIST should perform better than the IN. I of course am too lethargic today to do this test myself.

Incidentally, are you aware that "indices", the correct plural for "index", is marked as an incorrect word by default in the inbuilt Firefox dictionary?