July 26, 2005 at 6:43 am
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!
July 26, 2005 at 8:42 am
Use a left join
select.....ot.ColumnFromOtherTable.......
from FROM dbo.QUALITY_TEST qt
left join OtherDataTable ot on qt.Value = ot.Value
July 27, 2005 at 7:40 am
Try this subquery in the Select statement:
(select Count(*) from theScoresTable where Agent_Name = dbo.QUALITY_TEST.agent_name and QUALITY_SCORE > 82.5) AS [Total Passes]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply