Display Record When A Course Has Not Been Taken

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Display Record When A Course Has Not Been Taken

Author

Topic

aturner
Starting Member

29 Posts

Posted - 2004-09-24 : 09:29:42

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 individual listed 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 that shows when they have not this course, 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 records?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-24 : 09:39:42

which field determines wheather a person has or hasn't taken a course?

Go with the flow & have fun! Else fight the flow

aturner
Starting Member

29 Posts

Posted - 2004-09-24 : 16:26:04

The xclassname determines if a person has taken a class.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-24 : 16:33:25

Start out more simply...your requirement is

"Find all people who have not attended ADVANCED FIRST AID CPR"

That should be simple enough.

How about some DDL and sample data

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-24 : 16:48:19

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39731

Until you start giving us more information and some sample data and help us out, we cannot possibly help you.

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-24 : 19:34:12

well i guess then you would do:
HAVING north_data.class <> 'ADVANCED FIRST AID CPR'
but why don't you put all that conditions in having into where clause??

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources