Display Records When A Staff Has Not Attended A Class
-
I need a way within a stored procedure to search for all records when an individual has not attended an ADVANCED FIRST AID CPR course. The variables require a trainer to search by xstartdate and xenddate with the following SELECT statement:
SELECT DISTINCT xlastname + ', ' + xfirstname + ' ' + xmidname as FULLNAME,
xempnum,
xssn,
xrank,
xdept,
xclassname
FROM north_data.person
INNER JOIN north_data.person
ON north_data.event = xuniquekey and north_data.class = yuniquekey
INNER JOIN north_data.event
ON north_data.class = xlocator and north_data.event = xlocator
WHERE north_data.xstartdate BETWEEN @xstartdate AND @xenddate
GROUP BY xlastname,
xfirstname,
xmidname,
xempnum,
xssn,
xrank,
xdept,
xclassname
HAVING north_data.class = 'ADVANCED FIRST AID CPR'
AND (north_data_rank = 'EXEC'
OR north_data_rank = 'SEC'
OR north_data_rank = 'EXECOFR'
OR north_data_rank = 'MGR'
When I run the above stored procedure, it returns value where individuals under the ranks above have already taken the course. But, that is not the return I'm looking for. I need to return records within the dates I search on, which shows when course has not been taken so that a notice can be sent to each individual.
Looking at the SELECT statement above, how can I change this statement to insure I retrieve the correct results within the date parameters I specify?
-
This was removed by the editor as SPAM
-
This is often a problem...can you show me what isn't there?
Anyway, if you change your having clause to <> classname you'll get a list of all the people who have taken other classes. You'll need to perform a dual pass of the data. Here is an example of what I mean.
select firstname, lastname from tEmployees where employeeID not in (select employeeID from tClasses where Classname = 'CPR Training')
The subquery is a listing of all the employee ID's for people who have taken the course so a listing of the employees where their ID isn't in the subquery would be a list of employees that have not taken the course.
Hope this helps.
If the phone doesn't ring...It's me.
-
try using
LEFT OUT JOIN north_data.event
ON north_data.class = xlocator and north_data.event = xlocator
WHERE north_data.class IS NULL
Francis
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply