Determine When Someon Has Not Taken A Course

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)

Determine When Someon Has Not Taken A Course

Author

Topic

aturner
Starting Member

29 Posts

Posted - 2004-09-10 : 13:39:00

I've been task with creating a Crystal Report based upon field parameters from the Pathlore LMS application. A supervisor would like the ability to determine when an employee has not completed a course within a date range. Here is the stored procedure I have created as follows:

CREATE PROCEDURE dbo.sp_UOF_FAILQUAL
@ReportBeginDate datetime,
@ReportEndDate datetime,
@employee_s varchar(1)
AS
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT
north_data.person.xlastname + ', ' + north_data.person.xfirstname AS EmpName,
north_data.person.agency,
north_data.person.rank,
north_data.person.employee_n
FROM north_data.person
LEFT JOIN north_data.event
ON north_data.person.xuniquekey = north_data.event.yuniquekey
ORDER BY north_data.person.xlastname + ', ' + north_data.person.xfirstname

There are three tables I am bases this stored procedure on: 1) Class table; 2) Event table; and 3) Person table. Both the Class and Event table are linked by a field called xlocator, which is a varchar field of 20 characters. The xlocator number identifies the type of course being offered, of which, each class is given a unique number. However, the class may be the same name with variant differences, such as: UOF-16 (1st Tri), UOF-27 (1st Tri) or UOF-08 (2nd Tri).

Question. How can I associate the xlocator of the Class and Event Table to show that an individual has not taken this course using the stored procedure above?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-10 : 13:44:57

Please give us a few create table statements, and some INSERT statments that will generate sample data, and what the results should be based on that sample data. Just include relevant tables, columns and data to this specific problem. If we can cut and paste your code into QA and then just write the SELECT statement at the end, it will be much easier for us to help you out and to give you a solution that you can just cut and paste, that will work well with your specific data.

That way we can work on figuring out the ANSWER, not trying to figure out the QUESTION.

Makes sense?

Please check out: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090

and by the way -- never ORDER BY an expression like that if you can avoid it; in this case you should say: ORDER BY north_data.person.xlastname, north_data.person.xfirstname

- Jeff

aturner
Starting Member

29 Posts

Posted - 2004-09-10 : 13:55:49

Here are the tables:

CREATE TABLE north_data.class (
xlocator varchar (20) NOT NULL ,
xcode varchar (10) NOT NULL ,
xname varchar (80) NOT NULL ,
xstartdate datetime NOT NULL ,
xenddate datetime NULL ,
xstarttime varchar (8) NULL ,
xendtime varchar (8) NULL ,
xhours numeric(28, 10) NULL ,
xsessions numeric(18, 0) NULL ,
xseatchg numeric(28, 10) NULL ,
xteacher varchar (30) NULL ,
xroom varchar (20) NULL ,
xmincap numeric(4, 0) NULL ,
xmaxcap numeric(4, 0) NOT NULL ,
xeclose datetime NULL ,
xwclose datetime NULL ,
xcclose datetime NULL ,
xbilldate datetime NULL ,
xbillfee numeric(28, 10) NULL ,
xevalclose datetime NULL ,
xfreealloc datetime NULL ,
xfreeelig datetime NULL ,
xprereq varchar (1) NULL ,
xcconflict varchar (1) NULL ,
xtconflict varchar (1) NULL ,
xexccap varchar (1) NULL ,
xautowait varchar (1) NULL ,
xowner varchar (10) NULL ,
xuser varchar (10) NOT NULL ,
xupdate datetime NOT NULL ,
ximport datetime NULL ,
xmustcall varchar (1) NULL ,
xselfhid varchar (1) NULL ,
xselfwok varchar (1) NULL ,
chas_note$ varchar (1) NOT NULL ,
ccontexte$ numeric(4, 0) NOT NULL ,
ccontextw$ numeric(4, 0) NOT NULL ,
ccontextc$ numeric(4, 0) NOT NULL ,
ccontextb$ numeric(4, 0) NOT NULL ,
ccontextf$ numeric(4, 0) NOT NULL ,
ccontextm$ numeric(4, 0) NOT NULL ,
cfreeseat$ numeric(4, 0) NOT NULL ,
xcosttrack varchar (1) NULL ,
cis_full$ varchar (1) NOT NULL ,
xvirtonly varchar (1) NULL ,
xorigin varchar (2) NOT NULL ,
yrectype varchar (1) NOT NULL ,
ycapfield varchar (30) NULL ,
xregowner varchar (1) NULL ,
xvcsid varchar (30) NULL ,
xvcsclssid varchar (128) NULL ,
xtimezone varchar (80) NULL ,
xcreate datetime NULL ,
xplan varchar (1) NULL ,
day varchar (10) NULL ,
evoc_agfee varchar (15) NULL ,
evoc_ctfee varchar (15) NULL ,
evoc_instr varchar (21) NULL ,
evoc_ofees varchar (15) NULL ,
lab_hours varchar (3) NULL ,
lecture_ho varchar (4) NULL ,
needed_equ varchar (40) NULL ,
offsite varchar (40) NULL ,
post_cert varchar (30) NULL ,
post_plan varchar (4) NULL ,
prerequisi varchar (20) NULL ,
provider varchar (35) NULL ,
sbso_fee varchar (15) NULL ,
sbvc_fee varchar (15) NULL ,
semester varchar (10) NULL ,
units varchar (10) NULL ,
user001 varchar (20) NULL ,
user002 varchar (21) NULL ,
user003 varchar (35) NULL ,
xonline varchar (128) NULL ,
xregmail varchar (1) NULL ,
xapproval numeric(1, 0) NULL ,
xappclose datetime NULL ,
xappdays numeric(3, 0) NULL ,
xchngemail varchar (1) NULL ,
xcontentpw varchar (30) NULL ,
xcntcredit numeric(1, 0) NOT NULL ,
xcntmonths numeric(4, 0) NOT NULL ,
xcntwin varchar (1) NULL ,
xselffin varchar (1) NULL ,
xlnchscrn varchar (1) NULL ,
xoutlook varchar (1) NULL ,
ypretest varchar (1) NULL ,
xmasterytp numeric(1, 0) NULL ,
xmasterypc numeric(3, 0) NULL ,
xregmonths numeric(4, 0) NULL
) ON PRIMARY
GO

CREATE TABLE north_data.event (
xstatus varchar (1) NOT NULL ,
xdate datetime NOT NULL ,
xtime varchar (8) NOT NULL ,
xgroup$ varchar (1) NULL ,
xgroupdate datetime NOT NULL ,
xgrouptime varchar (8) NOT NULL ,
xcreate datetime NOT NULL ,
xupdate datetime NOT NULL ,
xuser varchar (10) NOT NULL ,
ximport datetime NULL ,
rhas_note$ varchar (1) NOT NULL ,
xlocator varchar (20) NOT NULL ,
yuniquekey varchar (15) NOT NULL ,
xorigin varchar (2) NOT NULL ,
xnumber varchar (12) NOT NULL ,
xmaxscore numeric(9, 3) NULL ,
xrawscore numeric(9, 3) NULL ,
xtotaltime numeric(12, 0) NULL ,
xlasttime numeric(12, 0) NULL ,
xtotattemp numeric(12, 0) NULL ,
xendstatus numeric(6, 0) NULL ,
xstarted datetime NULL ,
xended datetime NULL ,
xattdate datetime NULL ,
yproj_ky numeric(15, 0) NULL ,
event_grad varchar (4) NULL ,
evoclassif varchar (5) NULL ,
firearms varchar (1) NULL ,
hand_cuff varchar (2) NULL ,
hrs_comple numeric(28, 10) NULL ,
rank varchar (15) NULL ,
reg_event varchar (10) NULL ,
sbsdstatn varchar (18) NULL ,
user001 datetime NULL ,
user002 datetime NULL ,
xappreqdt datetime NULL
) ON PRIMARY
GO

CREATE TABLE north_data.person (
xuniquekey varchar (255) NOT NULL ,
xlastname varchar (128) NOT NULL ,
xfirstname varchar (128) NOT NULL ,
xmidname varchar (40) NULL ,
xemail varchar (80) NULL ,
xfax varchar (80) NULL ,
phas_note$ varchar (1) NOT NULL ,
xselfhid varchar (1) NULL ,
xupdate datetime NOT NULL ,
xuser varchar (10) NOT NULL ,
ximport datetime NULL ,
pname_fml$ varchar (286) NULL ,
pname_lfm$ varchar (286) NULL ,
pcontexte$ numeric(4, 0) NOT NULL ,
pcontextw$ numeric(4, 0) NOT NULL ,
pcontextc$ numeric(4, 0) NOT NULL ,
pcontextb$ numeric(4, 0) NOT NULL ,
pcontextf$ numeric(4, 0) NOT NULL ,
pcontextm$ numeric(4, 0) NOT NULL ,
xempstatus varchar (2) NULL ,
xtermdate datetime NULL ,
yinactive varchar (1) NOT NULL ,
xemail2 varchar (255) NULL ,
xother_st varchar (250) NULL ,
yprofil_ky numeric(15, 0) NOT NULL ,
xadmin_bl tinyint NOT NULL ,
xprofcode varchar (20) NULL ,
xpassword varchar (30) NULL ,
xuser_id varchar (20) NULL ,
xuser_name varchar (260) NULL ,
yuniquekey varchar (15) NOT NULL ,
agency varchar (40) NULL ,
agency_tel varchar (20) NULL ,
emergency varchar (12) NULL ,
employee_n varchar (6) NULL ,
employee_s varchar (1) NULL ,
evoclassif varchar (5) NULL ,
fax_no varchar (15) NULL ,
home_phone varchar (12) NULL ,
instructor varchar (20) NULL ,
mail_add varchar (45) NULL ,
mailing_ad varchar (40) NULL ,
pag_pho varchar (20) NULL ,
rank varchar (15) NULL ,
s123 numeric(28, 10) NULL ,
to_phone_n varchar (25) NULL ,
training_o varchar (45) NULL ,
user001 varchar (40) NULL ,
user002 varchar (10) NULL ,
user003 varchar (20) NULL ,
user004 varchar (20) NULL ,
xgroup1 varchar (18) NULL ,
xgroup2 varchar (80) NULL ,
xgroup3 varchar (80) NULL ,
xapprexmpt varchar (1) NULL ,
xapprover varchar (15) NULL ,
xinvatt numeric(3, 0) NOT NULL ,
xpwdexp datetime NOT NULL ,
xresetpwd numeric(1, 0) NOT NULL
) ON PRIMARY
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-10 : 14:03:30

Thanks, but i think you missed this part:

Just include relevant tables, columns and data to this specific problem

Please try to look at this from the point of view of those are trying to help you.

Thanks!

- Jeff

aturner
Starting Member

29 Posts

Posted - 2004-09-10 : 14:10:41

I hope this help:

CREATE TABLE north_data.class (
xlocator varchar (20) NOT NULL ,
xname varchar (80) NOT NULL ,

) ON PRIMARY
GO

CREATE TABLE north_data.event (
xlocator varchar (20) NOT NULL ,
yuniquekey varchar (15) NOT NULL ,

) ON PRIMARY
GO

CREATE TABLE north_data.person (
xuniquekey varchar (255) NOT NULL ,
xlastname varchar (128) NOT NULL ,
xfirstname varchar (128) NOT NULL ,
yuniquekey varchar (15) NOT NULL ,
agency varchar (40) NULL ,
employee_n varchar (6) NULL ,
employee_s varchar (1) NULL ,
rank varchar (15) NULL ,
) ON PRIMARY
GO

I'm not sure what data you would need.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-10 : 14:14:50

Well the data you need to see on the report....

INSERT INTO TABLE (Collist)
SELECT Collist UNION ALL
SELECT Collist UNION ALL
SELECT Collist UNION ALL
...

And what the results for that data should look like...

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-10 : 14:17:52

so where's the date range? I don't see how this will help me come up with a solution based on your original question.

sample data is just enough data so that we can run our query and make sure it generates the results you expect. Without being paid for this, there's not much incentive for me to spend 20 minutes typing up sample data so I can be sure I am giving you a good solution.

Granted, some of us here are insane and don't mind spending the time doing all that work, but after a while it gets frustrating. Don't mean to take it out on you.

most people find if they go through this exercise: Strip off the unnecessary or trivial parts of the problem, work on a small subset of data (instead of say, 10,000 rows) and get down to the core problem and guess what: they see the answer right in front of them and don't even NEED help at that point. Learning how to find the QUESTION is probably more challenging than finding the ANSWER. And only you can find the question, we can't help with that!

(I'm starting to sound like Yoda over here .... where's the Jedi Yak Master when you need him?)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources