WHEN [Web Price] > 0 AND [Web Price] <=2400 then 'Cheap' ELSE 'Cheap' END AS [Price Category] FROM [dbo].[AW_Products_Flattened] as p WHERE [Sub Category] = 'Mountain Bikes' This query will dynamically break the products into groups using the SELECT USE YOUR DATABASE not Feathermans CASE. The groups are USE [Featherman_Analytics]; a) a top end of products 1.5 times greater than the avg. web price for the sub-category DECLARE @AveragePrice as decimal = (SELECT AVG ([Web Price]) FROM [dbo].[AW_Products_Flattened] b) an expensive group of products 10% higher than the sub-category avg. WHERE [Sub Category] = 'Mountain Bikes') yet lower than the top end SELECT [ProductKey], [Part #], [Model], [Product], [Web Price] c) an moderate group of products lower than the expensive group yet higher , CASE than 75% of the sub-category avg. This is the middle group WHEN [Web > @AveragePrice *1.5 then 'Top End' d) a low end group of products that is less than 75% of the sub category avg. WHEN [Web Price] > @AveragePrice * 1.1 AND [Web Price] < (@AveragePrice * 1.5) then 'Expensive' WHEN [Web Price] > @AveragePrice * .75 AND [Web Price] <= @AveragePrice * 1.1 then 'Moderate' WHEN [Web Price] > 0 AND [Web Price] <=@AveragePrice * .75 then 'Low end' END AS [Price Category] FROM [dbo].[AW_Products_Flattened] as p WHERE [Sub Category] = 'Mountain Bikes' ORDER BY [Web Price] DESC
WHEN [Web Price] > 0 AND [Web Price] <=2400 then 'Cheap' ELSE 'Cheap' END AS [Price Category] FROM [dbo].[AW_Products_Flattened] as p WHERE [Sub Category] = 'Mountain Bikes' USE YOUR DATABASE not Feathermans USE [Featherman_Analytics]; DECLARE @AveragePrice as decimal = (SELECT AVG ([Web Price]) FROM [dbo].[AW_Products_Flattened] WHERE [Sub Category] = 'Mountain Bikes') SELECT [ProductKey], [Part #], [Model], [Product], [Web Price] , CASE WHEN [Web > @AveragePrice *1.5 then 'Top End' WHEN [Web Price] > @AveragePrice * 1.1 AND [Web Price] < (@AveragePrice * 1.5) then 'Expensive' WHEN [Web Price] > @AveragePrice * .75 AND [Web Price] <= @AveragePrice * 1.1 then 'Moderate' WHEN [Web Price] > 0 AND [Web Price] <=@AveragePrice * .75 then 'Low end' END AS [Price Category] FROM [dbo].[AW_Products_Flattened] as p WHERE [Sub Category] = 'Mountain Bikes' ORDER BY [Web Price] DESC This query will dynamically break the products into groups using the SELECT CASE. The groups are a) a top end of products 1.5 times greater than the avg. web price for the sub-category b) an expensive group of products 10% higher than the sub-category avg. yet lower than the top end c) an moderate group of products lower than the expensive group yet higher than 75% of the sub-category avg. This is the middle group d) a low end group of products that is less than 75% of the sub category avg.
Turning-your-Query-into-a-Stored-Procedure-final.docx