Query problem...Need Help!

• Salvatore Conti Jr.

  Ten Centuries

  Points: 1155

  More actions

  July 26, 2005 at 6:43 am

  #363257

  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!

• sara karasik

  SSCommitted

  Points: 1593

  More actions

  July 26, 2005 at 8:42 am

  #577190

  Use a left join

  select.....ot.ColumnFromOtherTable.......

  from FROM dbo.QUALITY_TEST qt

  left join OtherDataTable ot on qt.Value = ot.Value

• Steve Fine

  Ten Centuries

  Points: 1164

  More actions

  July 27, 2005 at 7:40 am

  #577532

  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]