Problem with SELECT stored proc

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

SQL Server Development (2000)

Problem with SELECT stored proc

Author

Topic

eslater
Starting Member

6 Posts

Posted - 2006-05-14 : 10:26:45

Here is the situation. We have 3 tables Employee, Training and TrainingType. Each employee can take courses any number of times. ie Joe Smith could take SQL Server training any number of times. Each type of training can be set to notify the system admin 30 days before it is coming due.

The problem is I am not sure how to pull which courses should be pulled for notification.

Consider table fields as such:
Employee:
EmployeeID,
FullName

Training:
TrainingID PK,
EmployeeID FK,
TrainingType FK,
ExpiryDate

TrainingType:
TrainingType PK,
Description,
Duration (how often the training is done in days),
NotifyDuration (how many days before due to notify),
Notify (boolean to indicate if Notification is on/off)

How would I go about pulling the notifications? I am guessing it may require cursors but I have no experience with them and have read they should be avoided if possible. Any help is appreciated.

Thanks,
eslater

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-14 : 10:52:36

Try this.

select *
from   TrainingType a inner join Training b
       on a.TrainingType = b.TrainingType
       inner join Employee c
       on b.EmployeeID = c.EmployeeID
where  ExpiryDate >= dateadd(day, datediff(day, 0, getdate()), -30)

If this is not what you want, please post some sample data and the expected result

eslater
Starting Member

6 Posts

Posted - 2006-05-14 : 12:03:34

Thanks for the quick response.

This doesnt quite do it. I have amended my original post as I missed a couple of things to do with the TrainingType.

The resultset should return records where notification date is <= todays date but only for the most recent Training done on the given Training Type. The notification date is the Trainings Expiry Date - NotifyDuration of the TrainingType.

Take the example where there is a TrainingType SQL Server where Notify is ON and the Duration is 365 Days and NotifyDuration is 30 Days. If an Employee takes the SQL Server training on March 1 2005 it will have an ExpiryDate of March 1 2006 and the notification date would be Feb 1 2006 (lets assume that was 30 days).

The complication occurs when the Employee has taken SQL Server training multiple times. If he took it March 1 2003, March 2 2004 and again March 1 2005 then we only want to return one notification which would happen March 1 2006 - 30 Days.

Thanks for your help,
Elliot

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-14 : 14:16:36

[code]
--Create table structures and data for testing. You should do this for us if you want quick answers. :)
DECLARE @Employee TABLE(
EmployeeID INT PRIMARY KEY,
FullName VARCHAR(255))

DECLARE @TrainingType TABLE(
TrainingType INT PRIMARY KEY,
Description VARCHAR(255),
Duration INT,--(how often the training is done in days),
NotifyDuration INT,--(how many days before due to notify),
Notify BIT)--(boolean to indicate if Notification is on/off)

DECLARE @Training TABLE(
TrainingID INT PRIMARY KEY,
EmployeeID INT,
TrainingType VARCHAR(255),
ExpiryDate DATETIME)

INSERT @Employee(EmployeeID, FullName)
SELECT 1,'Bob Bob' UNION ALL
SELECT 2,'Jim Bob' UNION ALL
SELECT 3,'Jim Jim'

INSERT @TrainingType(TrainingType, Description, Duration, NotifyDuration, Notify)
SELECT 1,'SQL Server Administration',120,30,1 UNION ALL
SELECT 2,'VB.NET Programming',120,30,1 UNION ALL
SELECT 3,'COBOL SUCKS',120,30,0 UNION ALL
SELECT 4,'FORTRAN Museum Training',120,30,0

INSERT @Training(TrainingID, EmployeeID, TrainingType, ExpiryDate)
SELECT 1,1,1,'04/01/2005' UNION ALL
SELECT 2,1,1,'08/01/2005' UNION ALL
SELECT 3,1,1,'12/01/2005' UNION ALL
SELECT 4,1,2,'12/01/2005' UNION ALL
SELECT 5,1,3,'12/01/2005' UNION ALL
SELECT 6,2,1,'08/01/2005' UNION ALL
SELECT 7,2,4,'12/01/2005' UNION ALL
SELECT 8,3,2,'08/01/2005'

DECLARE
@date_120_1 DATETIME,
@date_120_2 DATETIME,
@date_120_3 DATETIME

SELECT
@date_120_1 = DATEADD(DD,-30,'04/01/2005'),
@date_120_2 = DATEADD(DD,-30,'08/01/2005'),
@date_120_3 = DATEADD(DD,-30,'12/01/2005')

--Uncomment and run select statements to test data.
-- SELECT * FROM @Employee
-- SELECT * FROM @TrainingType
-- SELECT * FROM @Training
-- SELECT @date_120_1, @date_120_2, @date_120_3

--Code based on your requirements. Notice the three notification dates and the different result sets returned.
SELECT
e.EmployeeID,
e.FullName,
trt.TrainingType,
trt.Description,
@date_120_1 AS TodaysDate,
dt.ExpiryDate
FROM
@Employee e
INNER JOIN (
--Derived table to get last class subscribed to by each student for classes with active notifications (otherwise, you don't care).
SELECT
tr.EmployeeID,
tr.TrainingType,
MAX(tr.ExpiryDate) AS ExpiryDate
FROM
@Training tr
INNER JOIN @TrainingType trt ON tr.TrainingType = trt.TrainingType
WHERE
trt.Notify = 1
GROUP BY
tr.EmployeeID,
tr.TrainingType) dt ON e.EmployeeID = dt.EmployeeID
INNER JOIN @TrainingType trt ON dt.TrainingType = trt.TrainingType
WHERE
DATEADD(DD,-30,dt.ExpiryDate) = @date_120_1

SELECT
e.EmployeeID,
e.FullName,
trt.TrainingType,
trt.Description,
@date_120_1 AS TodaysDate,
dt.ExpiryDate
FROM
@Employee e
INNER JOIN (
--Derived table to get last class subscribed to by each student for classes with active notifications (otherwise, you don't care).
SELECT
tr.EmployeeID,
tr.TrainingType,
MAX(tr.ExpiryDate) AS ExpiryDate
FROM
@Training tr
INNER JOIN @TrainingType trt ON tr.TrainingType = trt.TrainingType
WHERE
trt.Notify = 1
GROUP BY
tr.EmployeeID,
tr.TrainingType) dt ON e.EmployeeID = dt.EmployeeID
INNER JOIN @TrainingType trt ON dt.TrainingType = trt.TrainingType
WHERE
DATEADD(DD,-30,dt.ExpiryDate) = @date_120_2

SELECT
e.EmployeeID,
e.FullName,
trt.TrainingType,
trt.Description,
@date_120_1 AS TodaysDate,
dt.ExpiryDate
FROM
@Employee e
INNER JOIN (
--Derived table to get last class subscribed to by each student for classes with active notifications (otherwise, you don't care).
SELECT
tr.EmployeeID,
tr.TrainingType,
MAX(tr.ExpiryDate) AS ExpiryDate
FROM
@Training tr
INNER JOIN @TrainingType trt ON tr.TrainingType = trt.TrainingType
WHERE
trt.Notify = 1
GROUP BY
tr.EmployeeID,
tr.TrainingType) dt ON e.EmployeeID = dt.EmployeeID
INNER JOIN @TrainingType trt ON dt.TrainingType = trt.TrainingType
WHERE
DATEADD(DD,-30,dt.ExpiryDate) = @date_120_3

[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources