Query Help....I'm sinking in tables and joins! PIC

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)

Query Help....I'm sinking in tables and joins! PIC

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-06-22 : 13:57:35

Hey all-
I need some help trying to figure out how to query this data in somewhat of a timely manner.

It is for training, so there are due warnings and past due warnings that need to go out.

When a course is created it is assigned to many different job codes and a cycle time - CO_CYCLE (365 days, 90 days, 30 days). Each user is a assigned a job code and dictates what training they need.

Each day I would like some stored procedure to run and spit out anyone that has training due within the next 30 days or has not had it at all.

The way the database has been designed has me somewhat perplexed on how to get the job done. All the right information is there needed to answer the question, but I am not sure how to make it happen.

I have the cycle time for a course and what jobcodes are assigned to it. I also know what jobcode is assigned to each user. I can also tell what class(s) have been given for each course and who has been to each and when (TRAINCLASSREC).

Please help me break this apart into workable parts or at least point me in the right direction.

Thanks,
Doug

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-06-23 : 09:25:02

Please let me know what other information I can provide. I am not looking for someone to write this for me, just point me in the right direction.

The way I see it this should be broken down into two queries. The first one would select the course along with the job codes.....

I am just going to post some of my hacks later today and see if that may help.

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-06-29 : 09:55:06

Ok- Here is my stored procedure that gets all training, but I know there has to be a better way to do this then what I have choosen. Sometimes, you need to just put something down and work from there. I am thinking that there could be the use of a sub-query, but I suck at them so I am hoping someone will lend a hand.

This stored procedure that spits out who needs what training and how long it has been since they last had it. If a user has never had training, the values are null. It's not done yet, I still need to find out if they are past due and send them a notice. In the past I've used a cursor and compiled each email and then sent it, all from within a stored procedure.

My main question is with about my logic...is there a better way to do this?

ALTER PROCEDURE spPASTDUE

AS

DECLARE @U_ID CHAR(5),
@U_FNAME VARCHAR(10),
@U_LNAME VARCHAR(20),
@CO_ID CHAR(6),
@CO_CYCLE INT,
@CO_DESC VARCHAR(150),
@TCR_DATE DATETIME,
@DAYSOLD INT

CREATE TABLE #TEMPUSERTRAINING (
TU_ID CHAR(5),
TU_FNAME VARCHAR(10),
TU_LNAME VARCHAR(20),
TCO_ID CHAR(6),
TCO_CYCLE INT,
TCO_DESC VARCHAR(150),
TCR_DATE DATETIME,
TDAYSOLD INT

)
--GET A LIST OF ACTIVE USER ID'S AND WHAT CLASSES ARE REQUIRED FOR THEIR JOB CODE
--FOR COURSES THAT HAVE BEEN IN THE SYSTEM MORE THEN 30 DAYS.

DECLARE USERCURSOR CURSOR FOR
SELECT [USER].U_ID, [USER].U_FNAME, [USER].U_LNAME, COURSE.CO_ID, COURSE.CO_CYCLE, COURSE.CO_DESC
FROM [USER] INNER JOIN
JOBCODE ON [USER].JC_ID = JOBCODE.JC_ID INNER JOIN
JC_CO ON JOBCODE.JC_ID = JC_CO.JC_ID INNER JOIN
COURSE ON JC_CO.CO_ID = COURSE.CO_ID
WHERE [USER].U_STATUS = 'A' AND DATEDIFF(DD,COURSE.CO_DATE,GETDATE()) > 30;
OPEN USERCURSOR

FETCH NEXT FROM USERCURSOR
INTO @U_ID, @U_FNAME, @U_LNAME, @CO_ID, @CO_CYCLE, @CO_DESC
WHILE @@FETCH_STATUS = 0

BEGIN
--GET THE TRAINING RECORD FOR THIS USER AND CLASS
--MAKE SURE YOU GET THE NEWEST
-- SELECT TOP 1 TRAINCLASSREC.CL_ID, TRAINCLASSREC.U_ID, COURSE.CO_ID, @TCR_DATE = TRAINCLASSREC.TCR_DATE,
-- DATEDIFF(dd, TRAINCLASSREC.TCR_DATE, GETDATE()) AS DAYSOLD, COURSE.CO_CYCLE
SELECT TOP 1 @TCR_DATE = TRAINCLASSREC.TCR_DATE,@DAYSOLD = DATEDIFF(dd, TRAINCLASSREC.TCR_DATE, GETDATE())
FROM TRAINCLASSREC INNER JOIN
CLASS ON TRAINCLASSREC.CL_ID = CLASS.CL_ID INNER JOIN
COURSE ON CLASS.CO_ID = COURSE.CO_ID
WHERE COURSE.CO_ID = @CO_ID AND TRAINCLASSREC.U_ID = @U_ID
ORDER BY TRAINCLASSREC.TCR_DATE DESC;

IF @@ROWCOUNT < 1
--THEY HAVE NEVER HAD TRAINING AND THE COURSE IS MORE THEN 30 DAYS OLD
BEGIN
PRINT 'NOPE, NO RECORDS'
INSERT INTO #TEMPUSERTRAINING(TU_ID, TU_FNAME, TU_LNAME, TCO_ID, TCO_CYCLE, TCO_DESC, TCR_DATE, TDAYSOLD)
VALUES(@U_ID, @U_FNAME, @U_LNAME, @CO_ID, @CO_CYCLE, @CO_DESC, NULL,NULL)
END
ELSE
BEGIN
--THEY HAVE HAD THE TRAINING AT LEAST ONCE, BUT STILL DONT KNOW IF PAST DUE
--COMPUTE THE DATES
PRINT 'YEP, THERE WAS A RECORD'
--SELECT @TCR_DATE = TRAINCLASSREC.TCR_DATE, @DAYSOLD = DAYSOLD;
INSERT INTO #TEMPUSERTRAINING(TU_ID, TU_FNAME, TU_LNAME, TCO_ID, TCO_CYCLE, TCO_DESC, TCR_DATE, TDAYSOLD)
VALUES(@U_ID, @U_FNAME, @U_LNAME, @CO_ID, @CO_CYCLE, @CO_DESC, @TCR_DATE, @DAYSOLD)
END

FETCH NEXT FROM USERCURSOR
INTO @U_ID, @U_FNAME, @U_LNAME, @CO_ID, @CO_CYCLE, @CO_DESC
END

CLOSE USERCURSOR
DEALLOCATE USERCURSOR

SELECT * FROM #TEMPUSERTRAINING

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources