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.
