Query Fails to retun anything - 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

Transact-SQL (2000)

Query Fails to retun anything

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2009-01-28 : 17:00:51

This query relates to 2 databases. In the EmployeeInfo table, each employee is assigned a unique number NUM. In the TestResults table, each employee is identified by their full name, which is composed of FIRSTNAME+' '+LASTNAME.

Here are the problems:

The FIRSTNAME and LASTNAME in the Employee Table may include spaces (minor annoyance).

If an Employee's LASTNAME is changed by HR (generally due to marital status changes), historic records in the TestResults table will not match up with an employee in the current EmployeeInfo table.

We have reports that show how much work each employee has done. The display format for an employee is "FIRSTNAME+' '+LASTNAME+' ('+NUM+')', but if there is a name change, that employee does not show up since there is no match.

I've attempted to write a query that will return zeros for an employee number NUM so that Management will be able to spot the employees that have had name changes and so these employees will not be dropped from the report.

However, as my stored procedure sits below, it returns 0 records when there should be about 200.

Could someone tell me what I've done wrong?

declare @SysID varchar(50), @DateStart DateTime, @DateEnd DateTime
set @SysID = 'decay'
set @DateStart='12/28/2008'
set @DateEnd='01/25/2009'
	SELECT 	CASE WHEN Count(EI.[NUM])=0 THEN Ops.[OP_ID]+' (000000)' ELSE EI.[FIRSTNAME]+' '+EI.[LASTNAME]+' ('+EI.[NUM]+')' END AS Operator,
		CASE WHEN Count(EI.[NUM])=0 THEN '0' ELSE EI.[Shift] END AS Shift
	FROM	TestResults Ops, EmployeeInfo EI
	WHERE
		(Ops.[System_ID] LIKE '%'+@SysID+'%') AND
		(Ops.[Date_Time] BETWEEN @DateStart AND @DateEnd) AND
		(Ops.Serial_Number NOT IN (SELECT * FROM SNFilter)) AND
		((Ops.[OP_ID] Like EI.[FIRSTNAME]+'%') AND (Ops.[OP_ID] Like '%'+EI.[LASTNAME]))
	GROUP BY EI.[Shift], EI.[NUM], EI.[FIRSTNAME], EI.[LASTNAME], Ops.[OP_ID]

I appreciate any help.

Joe

Avoid Sears Home Improvement

jp2code
Posting Yak Master

175 Posts

Posted - 2009-01-30 : 09:11:39

Well, it is 2 days later and I got this to work on my own.

For the record, here is what I found to work for me:

declare @SysID varchar(50), @DateStart DateTime, @DateEnd DateTime
set @SysID = 'decay'
set @DateStart='12/28/2008'
set @DateEnd='01/25/2009'

	SELECT DISTINCT Operator, Shift
	FROM (
		SELECT DISTINCT EI.[FIRSTNAME]+' '+EI.[LASTNAME]+' ('+EI.[NUM]+')' AS 'Operator', EI.[Shift] AS 'Shift' 
		FROM (	SELECT [OP_ID] FROM TestResults
			WHERE 	([System_ID] LIKE '%'+@SysID+'%') AND
				([Date_Time] BETWEEN @DateStart AND @DateEnd) AND
				(Serial_Number NOT IN (SELECT * FROM SNFilter))
		) Ops INNER JOIN EmployeeInfo EI ON Ops.[OP_ID]=EI.[NUM]
		UNION
		SELECT DISTINCT [OP_ID]+' (000000)' AS 'Operator', 0 AS 'Shift' FROM TestResults
		WHERE 	([System_ID] LIKE '%'+@SysID+'%') AND
			([Date_Time] BETWEEN @DateStart AND @DateEnd) AND
			(Serial_Number NOT IN (SELECT * FROM SNFilter))
	) T1
	ORDER BY Shift, Operator

I hope someone down the line finds something useful in it.

Avoid Sears Home Improvement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources