You could also do this with a
CTE
, populating it with the Customer & Average Rating.
Then you would
JOIN
that CTE along with the Ratings table.
Proof of Concept:
DECLARE @Ratings TABLE ( Rating Decimal(5,1) NULL, RatingDesc NVARCHAR(16) NULL )
INSERT @Ratings VALUES (1, 'poor'), (2, 'good'), (3, 'v-good'), (4, 'excellent'), (5, 'Off the chart')
DECLARE @Reviews TABLE ( Customer INT NULL, Rating Decimal(5,1) NULL)
INSERT @Reviews VALUES (1, 1), (1,2), (2, 5), (3,3), (3,3)
; WITH cte AS (
SELECT Customer, AvgRating = Cast(Avg(Rating) as Decimal(5,1))
FROM @Reviews
GROUP BY Customer
)
SELECT cte.*, rv.RatingDesc
FROM cte
INNER JOIN @Ratings rv
ON cte.AvgRating BETWEEN rv.Rating - 0.1 AND rv.Rating + 0.9