SQL not yielding expected results

Question

I have three tables related to this particular query:

• Lawson_Employees: LawsonID (pk), LastName, FirstName, AccCode (numeric)
• Lawson_DeptInfo: AccCode (pk), AccCode2 (don't ask, HR set up), DisplayName
• tblExpirationDates: EmpID (pk), ACLS (date), EP (date), CPR (date), CPR_Imported (date), PALS (date), Note

The goal is to get the data I need to report on all those who have already expired in one or more certification, or are going to expire in the next 90 days.

Some important notes:

• This is being run as part of a vbScript, so the 90-day date is being calculated when the script is run. I'm using 2010-08-31 as a placeholder since its the result at the time this question is being posted.
• All cards expire at the end of the month. (which is why the above date is for the end of August and not 90 days on the dot)
• A valid EP card supersedes ACLS certification, but only the latter is required of some employees. (wasn't going to worry about it until I got this question answered, but if I can get the help I'll take it)
• The CPR column contains the expiration date for the last class they took with us. (NULL if they didn't take any classes with us)
• The CPR_Imported column contains the expiration date for the last class they took somewhere else. (NULL if they didn't take it elsewhere, and bravo for following policy)
• The distinction between CPR classes is important for other reports. For purposes of this report, all we really care about is which one is the most current - or at least is currently current.
• If I have to, I'll ignore ACLS and PALS for the time being as it is non-compliance with CPR training that is the big issue at the moment. (not that the others won't be, but they weren't mentioned in the last meeting...)

Here's the query I have so far, which is giving me good data:

SELECT 
    iEmp.LawsonID, iEmp.LastName, iEmp.FirstName, 
    dept.AccCode2, dept.DisplayName, 
    Exp.ACLS, Exp.EP, Exp.CPR, Exp.CPR_Imported, Exp.PALS, Exp.Note 
FROM (Lawson_Employees AS iEmp 
        LEFT JOIN Lawson_DeptInfo AS dept ON dept.AccCode = iEmp.AccCode) 
        LEFT JOIN tblExpirationDates AS Exp ON iEmp.LawsonID = Exp.EmpID 
WHERE iEmp.CurrentEmp = 1 
    AND ((Exp.ACLS <= #2010-08-31# 
            AND Exp.ACLS IS NOT NULL)
        OR (Exp.CPR <= #2010-08-31# 
            AND Exp.CPR_Imported <= #2010-08-31#) 
        OR (Exp.PALS <= #2010-08-31# 
            AND Exp.PALS IS NOT NULL))
ORDER BY dept.AccCode2, iEmp.LastName, iEmp.FirstName;

After perusing the result set, I think I'm missing some expiration dates that should be in the result set. Am I missing something? This is the sucky part of being the only developer in the department... no one to ask for a little help.

Answer 1

Consider these two conditions from your current WHERE clause:

Exp.ACLS <= #2010-08-31# AND Exp.ACLS IS NOT NULL
Exp.PALS <= #2010-08-31# AND Exp.PALS IS NOT NULL

Do you see different results if you change them as follows?

Exp.ACLS <= #2010-08-31#
Exp.PALS <= #2010-08-31#

I thought placing a condition (such as <= #2010-08-31#) on a column automatically excludes rows with Null values in that column.

I doubt those changes would solve your problem. But, if I'm wrong, it would be good to know. Frankly, I'm stumped by this statement:

After perusing the result set, I think I'm missing some expiration dates that should be in the result set.

Can you show us some sample rows which should included in your resultset, but aren't?

Answer 2

I think the problem is here:

 OR (Exp.CPR <= #2010-08-31# 
            AND Exp.CPR_Imported <= #2010-08-31#) 

Null is not less than or greater than anything.

If you need the people who do not have a valid CPR, you will need to include nulls.

It may be easiest to use Nz:

 OR (Nz(Exp.CPR,#2010-08-31#) <= #2010-08-31# 
            AND Nz(Exp.CPR_Imported,#2010-08-31#) <= #2010-08-31#)