dB design help - SQL Server Forums

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)

dB design help

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-09-10 : 12:30:44

Need help on this database design please.

What I am trying to achive is how can I get an employee to show the correct trainer for the site he/she belongs to.

Example:
Employee pin # 1 would show that the trainer is pin # 5 for 'wi' (WI46).
Employee pin # 2 would show that the trainer is pin # 8 for 'wi' (WI46).

NOTE:
sc = sc_trainer
cam = cam_trainer
lut = lut_trainer
sp = sp_trainer

****************************************************

CREATE TABLE [dbo].[iso_employwi] (
[empWiID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[pin] [int] NOT NULL ,
[wi] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[rev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('1','WI46','C')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('2','WI46','C')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('3','WI46','C')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('4','WI46','C')

GO

CREATE TABLE [dbo].[iso_employees] (
[pin] [int] NOT NULL ,
[lname] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fname] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[site] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
INSERT INTO iso_employees (pin,lname,fname,site,email)
VALUES ('1','Lupin','John','sc','John@hotmail.com')
INSERT INTO iso_employees (pin,lname,fname,site,email)
VALUES ('2','Martin','Jack','cam','Jack@hotmail.com')
INSERT INTO iso_employees (pin,lname,fname,site,email)
VALUES ('3','Judd','Kristy','lut','Kristy@hotmail.com')
INSERT INTO iso_employees (pin,lname,fname,site,email)
VALUES ('4','Judd','Cindy','sp','Cindy@hotmail.com')
INSERT INTO iso_employees (pin,lname,fname,site,email)
VALUES ('5','Croft','Sarah','sc','Sarah@hotmail.com')
INSERT INTO iso_employees (pin,lname,fname,site,email)
VALUES ('6','Rex','Tom','lut','Tom@hotmail.com')
INSERT INTO iso_employees (pin,lname,fname,site,email)
VALUES ('7','Blake','Frank','sp','Frank@hotmail.com')
INSERT INTO iso_employees (pin,lname,fname,site,email)
VALUES ('8','Russ','Steve','Cam','Steve@hotmail.com')

GO

CREATE TABLE [dbo].[iso_wi] (
[wi] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[wi_title] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sc_trainer] [int] NULL ,
[cam_trainer] [int] NULL ,
[lut_trainer] [int] NULL ,
[sp_trainer] [int] NULL ,
) ON [PRIMARY]
INSERT INTO iso_wi (wi,wi_title,sc_trainer,cam_trainer,lut_trainer,sp_trainer)
VALUES ('WI46','Email Procedure','5','8','6','7')

GO

DROP TABLE dbo.iso_employwi
DROP TABLE dbo.iso_employees
DROP TABLE dbo.iso_wi

****************************************************

Below is part of some code that I use in 'Crystal Reports' to achive what I need and it works fine, but I would also like to see if I can do this in SQL. Maybe a stored procedure where a pin gets passed to it and it returns the trainers pin?

---------------
CRYSTAL REPORTS formula:

IF {iso_employees.Site} = "SC" THEN {iso_wi.SC_Trainer}
ELSE IF {iso_employees.Site} = "CAM" THEN {iso_wi.CAM_Trainer}
ELSE IF {iso_employees.Site} = "LUT" THEN {iso_wi.LUT_Trainer}
ELSE IF {iso_employees.Site} = "SP" THEN {iso_wi.SP_Trainer}
ELSE ""
---------------

Thanks in adv, JLM

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-10 : 13:50:20

First, let me say, that's a great post (maybe a little confusing), but any post with DDL, DML AND sample data is a GREAT post!


SELECT 	  e.PIN
	, CASE  WHEN e.Site = 'SC'  THEN i.SC_Trainer
		WHEN e.Site = 'CAM' THEN i.CAM_Trainer
		WHEN e.Site = 'LUT' THEN i.LUT_Trainer
		WHEN e.Site = 'SP'  THEN i.SP_Trainer
				    ELSE ''
	END AS whatever
   FROM iso_employees e 
  JOIN  iso_employwi w 
    ON  e.pin = w.pin 
  JOIN  iso_wi i 
    ON  i.wi = w.wi
GO

Brett

8-)

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-09-10 : 18:51:11

Thanks Brett! That worked great for our web report.

After showing this to my manager, she now is asking if we could also use this code
in some sort of trigger that will email the trainer when an employee updates their
record. The trainer will get an email of what WI that employee updated.

For instance, say a user goes to update their status 'rev' for a wi in the table (iso_employwi). Once this happens, we would like a trigger of some sort that will pass the trainers pin number, employee's pin number and the WI that the employee updated to a stored procedure that we have created (@empin, @trpin, @wiupdt) that will take care of the emailing.

This is how I saved your procedure and used it for a report.

********************************

CREATE PROCEDURE [dbo].[sp_isowi_trainer]
@emppin varchar(15),
@empwi nvarchar(50)
AS
SELECT e.PIN
, CASE WHEN e.Site = 'SC' THEN i.SC_Trainer
WHEN e.Site = 'CAM' THEN i.CAM_Trainer
WHEN e.Site = 'LUT' THEN i.LUT_Trainer
WHEN e.Site = 'SP' THEN i.SP_Trainer
ELSE ''
END AS trpin
FROM iso_employees e
JOIN iso_employwi w
ON e.pin = w.pin
JOIN iso_wi i
ON i.wi = w.wi
WHERE e.pin = @emppin AND w.wi = @empwi
GO

********************************

Its funny sometimes that once you finish something for your manager and they like, but
see that maybe something else can be made from it, they ask for more. Will it ever be
enough? ;-)

JLM

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-09-11 : 02:41:02

quote:
Originally posted by jose1lm
Will it ever be enough? ;-)

7 years and I've still to see if it will ever be enough ;-)
but again, thats why we still have our jobs ain't?

anyways, you can use a trigger on the table for UPDATE statement to look at "INSERTED" table (this table is populated during the Update statement) to get the new updated value and process accordingly.

refer BOL for more on Triggers and "INSERTED" table

But if you plan to e-mail, I suggest to keep mailing out of the scope of the trigger, as this would degrade the performance of your appication. Alternative methods, say, by populating a new table in the trigger with the details to be e-mailed and have an other scheduled job scan this table periodically for any new records; can be implemented.

Hemanth Gorijala
BI Architect / DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources