nvanhaaster
3/27/2007 8:42:00 PM
Hey All,
Just wanted to say i am still green with writing complex SQL Queries
so please bear with me.
I have 2 tables 1 containing an employee number (table:1) which is
bound to the type of system they are using and another table which
specifies the universal employee number(tabel: 2) for that same
person.
I need to be able to reference if there are any records in table 1
that do not have a universal number in table two.
The dilema comes here. Table 2 (universal table) has 4 different
columns of employee numbers. All specific to the system they are
using.
Here is the general layout
Table 1: PK on ID Date Time Location
------------------------------------------------
| ID | Date | Time | Location |
-----------------------------------------------
|0001 | 3/1/07 | 9:50 | 103 |
-----------------------------------------------
|3498 | 3/3/07 | 19:35| 987 |
-----------------------------------------------
|1587 | 3/1/07 | 9:53 | 456 |
-----------------------------------------------
Table 2: PK on UnivID & Location
-------------------------------------------------------------------------------------
| UnivID | Location | ID1 | ID 2 | ID 3 | ID
4 |
-------------------------------------------------------------------------------------
|1030001 | 103 | 0001 | | |
1587 |
--------------------------------------------------------------------------------------
|1030002 | 103 | 0002 | 3498 | 9741 | 1487 |
--------------------------------------------------------------------------------------
What i would like to do is reference table 1 against table 2 to first
make sure that every employee in table 1 has a record in table 2.
I have been looking for days now and come up with the best solution.
Hopefully someone here has a good idea.
I have created this procedure just must be a better way to do this as
I have to display the results in the browser.
ALTER PROCEDURE sp_rnetGet
@Loc VARCHAR(50),
@SDate VARCHAR(10),
@EDate VARCHAR(10)
AS
DECLARE @sql VARCHAR(5000)
DECLARE @ID VARCHAR(50)
DECLARE @location VARCHAR(3)
DECLARE projectCursor CURSOR FOR
SELECT
DISTINCT(employee),
location
FROM
table1
WHERE
location=@Loc AND
callDate BETWEEN @SDate AND @EDate
OPEN projectCursor
FETCH NEXT FROM projectCursor
INTO
@ID,
@location
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = '
IF NOT EXISTS (
(SELECT
univID
FROM
table2
WHERE
rAvayaID='''+@ID+''' OR
xmAvayaID='''+@ID+''' OR
lclID='''+@ID+''' OR
nyID='''+@ID+''' AND
location='''+@location+''')
)
BEGIN
SELECT
DISTINCT(ID,location
FROM
table1
WHERE
ID='''+@ID+''' AND
location='''+@location+''' AND
callDate BETWEEN '''+@SDate+''' AND '''+@EDate+'''
END
'
EXEC(@sql)
FETCH NEXT FROM projectCursor
INTO
@ID,
@location
END
CLOSE projectCursor
DEALLOCATE projectCursor