Find Aggregate value in lookup

Question

Post reply

Find Aggregate value in lookup

ehlinger

Hall of Fame

Points: 3585
More actions
September 10, 2008 at 9:10 pm

#190571

I have a table of responses to surveys that I am averaging the responses by employee and a category (expectation). I need to take those averages and see where they fall into a lookup range and and return the corresponding score along with the employee number and category.
Here is a basic example of the tables and some test data:
CREATE TABLE [dbo].[response](
[resp_id] [int] IDENTITY(1,1) NOT NULL,
[response] [int] NULL,
[expectation] [varchar](50) NULL,
[employeenumber] [int] NULL,
[plmeasureID] [int] NULL,
CONSTRAINT [PK_response] PRIMARY KEY CLUSTERED
(
[resp_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(7, 'Build Relationships', 2, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(8, 'Embrace Change', 2, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(9, 'Maintain a High Work Ethic', 2, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(7, 'Build Relationships', 2, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(6, 'Embrace Change', 2, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(7, 'Maintain a High Work Ethic', 2, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(5, 'Build Relationships', 2, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(6, 'Embrace Change', 2, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(6, 'Maintain a High Work Ethic', 2, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(6, 'Build Relationships', 7, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(7, 'Embrace Change', 7, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(7, 'Maintain a High Work Ethic', 7, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(6, 'Build Relationships', 7, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(5, 'Embrace Change', 7, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(7, 'Maintain a High Work Ethic', 7, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(5, 'Build Relationships', 7, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(7, 'Embrace Change', 7, 13)
INSERT into response (response, expectation, employeenumber, plmeasureID)
VALUES(5, 'Maintain a High Work Ethic', 7, 13)
CREATE TABLE [dbo].[perfLevels](
[plid] [int] IDENTITY(1,1) NOT NULL,
[plmeasureid] [int] NULL,
[min_target] [int] NULL,
[max_target] [int] NULL,
[perf_level] [int] NULL,
CONSTRAINT [PK_perfLevels] PRIMARY KEY CLUSTERED
(
[plid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT into perfLevels (plmeasureid, min_target, max_target, perf_level)
VALUES (13, 0, 1, 1)
INSERT into perfLevels (plmeasureid, min_target, max_target, perf_level)
VALUES (13, 1, 2, 2)
INSERT into perfLevels (plmeasureid, min_target, max_target, perf_level)
VALUES (13, 2, 3, 3)
INSERT into perfLevels (plmeasureid, min_target, max_target, perf_level)
VALUES (13, 3, 4, 4)
INSERT into perfLevels (plmeasureid, min_target, max_target, perf_level)
VALUES (13, 4, 5, 5)
INSERT into perfLevels (plmeasureid, min_target, max_target, perf_level)
VALUES (13, 5, 6, 6)
INSERT into perfLevels (plmeasureid, min_target, max_target, perf_level)
VALUES (13, 6, 7, 7)
INSERT into perfLevels (plmeasureid, min_target, max_target, perf_level)
VALUES (13, 7, 8, 8)
INSERT into perfLevels (plmeasureid, min_target, max_target, perf_level)
VALUES (13, 8, 9, 9)
I've put together this query but it doesn't give me what I want because it is returning me all nine performance levels per employee:
SELECT --ROUND(CAST(AVG(CAST(r.response AS DECIMAL (3,1))) AS DECIMAL(3,0)), 0) AS [Score],
CASE WHEN ROUND(CAST(AVG(CAST(r.response AS DECIMAL (3,2))) AS DECIMAL(4,2)), 0) > p.min_target AND ROUND(CAST(AVG(CAST(r.response AS DECIMAL (3,2))) AS DECIMAL(4,2)), 0) <= p.max_target THEN p.perf_level END AS [Score],
r.expectation,
r.employeenumber
FROM response r JOIN perfLevels p ON r.plmeasureID = p.plmeasureid
GROUP BY r.expectation,
r.employeenumber,
p.min_target,
p.max_target,
p.perf_level
What I want to return would be similar to the following:
Score Expectation EmployeeNumber
6 Build Relationships 2
7 Embrace Change 2
7 Maintain a High Work Ethic 2
6 Build Relationships 7
6 Embrace Change 7
6 Maintain a High Work Ethic 7

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Something like this?

WITH AvgResponse AS (

SELECT AVG(CAST(response AS DECIMAL (4,2))) AS response,

expectation,

employeenumber,

plmeasureID

FROM dbo.response

GROUP BY expectation,employeenumber,plmeasureID

)

SELECT p.perf_level AS Score,

a.expectation AS Expectation,

a.employeenumber AS EmployeeNumber

FROM dbo.perfLevels p

INNER JOIN AvgResponse a ON a.plmeasureID=p.plmeasureID

AND a.response > p.min_target AND a.response <= p.max_target

ORDER BY a.employeenumber

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ehlinger Hall of Fame Points: 3585 More actions · Answer 2

That will work. Thanks.

Here is another way. Thoughts on the differences?

SELECT DISTINCT

Score,

expectation,

employeenumber

FROM(

SELECT CASE WHEN ROUND(CAST(AVG(CAST(r.response AS DECIMAL (3,2))) AS DECIMAL(4,2)), 0) > p.min_target

AND ROUND(CAST(AVG(CAST(r.response AS DECIMAL (3,2))) AS DECIMAL(4,2)), 0) <= p.max_target

THEN p.perf_level END AS [Score],

r.expectation,

r.employeenumber

FROM response r JOIN perfLevels p ON r.plmeasureID = p.plmeasureid

GROUP BY r.expectation,

r.employeenumber,

p.min_target,

p.max_target,

p.perf_level

)A

WHERE Score IS NOT NULL

ORDER BY employeenumber

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

As long as they both work correctly, it's up to you. You may want to check timings and execution plans in case there

are major differences. Also I think your version can be simplified to this

SELECT DISTINCT p.perf_level AS [Score],