A Possible Lookup Function?? Need Help!

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)

A Possible Lookup Function?? Need Help!

Author

Topic

Salnick4
Starting Member

19 Posts

Posted - 2005-07-26 : 09:03:44

I do not know if this is possible

, but I will explain anyway.

I need to write a query within my SQL Server 2000 that looks up values from a different table that will be used within an Aggregate function.

This would be similar to Excels lookup array function, where if a value we are searching equals the value in Column A, then retrieve the value in Column B???

Below is a snippet of the code that I am trying to tweak:

SELECT AGENT_NAME, COUNT(AGENT_NAME) AS [Total Scores], AVG(QUALITY_SCORE) AS [Average Score], 
SUM(CASE WHEN QUALITY_SCORE > 82.5 THEN 1 ELSE 0 END) AS [Total Passes]
FROM dbo.QUALITY_TEST
WHERE (EVALUATION_DTE BETWEEN '7/1/05 12:35:06 AM' AND '7/23/05 11:55:49 PM')
GROUP BY AGENT_NAME

You notice the CASE statement, it is there that I need to add some more code...I have to look up a specific agent from a seperate table, and retrieve their target for a Qaulity Score...this value would replace where it currently says 82.5.

I hope that I have explained myself well...

Please advise if there is something out there that I can use...

Thank you!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-26 : 09:24:32

you could use a subselect... and link them with a common id.

SELECT AGENT_NAME, COUNT(AGENT_NAME) AS [Total Scores], AVG(QUALITY_SCORE) AS [Average Score],
SUM(CASE WHEN QUALITY_SCORE > (select top 1 Qaulity_Score from otherTable where id = t1.id)
THEN 1 ELSE 0 END) AS [Total Passes]
FROM dbo.QUALITY_TEST t1
WHERE (EVALUATION_DTE BETWEEN '7/1/05 12:35:06 AM' AND '7/23/05 11:55:49 PM')
GROUP BY AGENT_NAME

Go with the flow & have fun! Else fight the flow

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-26 : 09:38:16

>>you could use a subselect... and link them with a common id

but it won't work, aggregate functions cannot contain subqueries.

maybe, it's reasonable to create user function like:

CREATE FUNCTION [dbo].[get_agent] (@agent_name varchar(100))
RETURNS int AS
BEGIN
return (select new_quality_score from separate_table where agent_name=@agent_name)
END

and after that just put it there:

SELECT AGENT_NAME, COUNT(AGENT_NAME) AS [Total Scores], AVG(QUALITY_SCORE) AS [Average Score],
SUM(CASE WHEN QUALITY_SCORE > dbo.get_agent(agent_name) THEN 1 ELSE 0 END) AS [Total Passes]
FROM dbo.QUALITY_TEST
WHERE (EVALUATION_DTE BETWEEN '7/1/05 12:35:06 AM' AND '7/23/05 11:55:49 PM')
GROUP BY AGENT_NAME

Salnick4
Starting Member

19 Posts

Posted - 2005-07-26 : 09:41:25

I will attempt to write the function suggested by Stalker..

Stay tuned ...to see if this works..

Thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-26 : 09:44:24

thanx for the correction...

Go with the flow & have fun! Else fight the flow

Salnick4
Starting Member

19 Posts

Posted - 2005-07-26 : 10:07:32

It worked great...this was the first time for me in building a USEWR DEFINED function...I appreciate the help.

Thank you very much!