USE [Featherman_Analytics]; DECLARE @NumberOrdersForEachCustomer TABLE Very often you will need to break the processing into several ([Customer Name] nvarchar(50), [Customer Category] nvarchar(30), [# Orders] numeric(6,2)) steps, such as before we created and loaded an array, dis some processing then used a separate UPDATE SET command to fill INSERT INTO @NumberOrdersForEachCustomer the column with data and get the job done. ([Customer Name], [Customer Category], [# Orders]) This iteration demonstrates that if the job is not too complex SELECT [Customer Name], [Customer Category] = you can put the CASE statement together in the SELECT statement. CASE WHEN [# Orders] > @AvgNumOrdersCorrect * 1.74 THEN 'Top Customer' Because this query does not use hard-coded ranges, and WHEN [# Orders] BETWEEN @AvgNumOrdersCorrect * 1.25 AND therefore is flexible to the dataset, it would intersting to slice @AvgNumOrdersCorrect * 1.74 THEN 'Good Customer' this data by region and/or year to see the pattern of customers. Here the 80/20 rule is not in effect, rather the sales are spread WHEN [# Orders] BETWEEN @AvgNumOrdersCorrect * .75 AND out. @AvgNumOrdersCorrect * 1.24 THEN 'Emerging Customer' WHEN [# Orders] < @AvgNumOrdersCorrect *.75 THEN 'Low Frequency Customer' END , COUNT(DISTINCT([Order ID])) as [# Orders] FROM [dbo].[SuperStore_Orders] GROUP BY [Customer Name] SELECT * FROM @NumberOrdersForEachCustomer SELECT [Customer Category], COUNT(*) as [Count] FROM @NumberOrdersForEachCustomer GROUP BY [Customer Category]
USE [Featherman_Analytics]; DECLARE @NumberOrdersForEachCustomer TABLE ([Customer Name] nvarchar(50), [Customer Category] nvarchar(30), [# Orders] numeric(6,2)) INSERT INTO @NumberOrdersForEachCustomer ([Customer Name], [Customer Category], [# Orders]) SELECT [Customer Name], [Customer Category] = CASE WHEN [# Orders] > @AvgNumOrdersCorrect * 1.74 THEN 'Top Customer' WHEN [# Orders] BETWEEN @AvgNumOrdersCorrect * 1.25 AND @AvgNumOrdersCorrect * 1.74 THEN 'Good Customer' WHEN [# Orders] BETWEEN @AvgNumOrdersCorrect * .75 AND @AvgNumOrdersCorrect * 1.24 THEN 'Emerging Customer' WHEN [# Orders] < @AvgNumOrdersCorrect *.75 THEN 'Low Frequency Customer' END , COUNT(DISTINCT([Order ID])) as [# Orders] FROM [dbo].[SuperStore_Orders] GROUP BY [Customer Name] SELECT * FROM @NumberOrdersForEachCustomer SELECT [Customer Category], COUNT(*) as [Count] FROM @NumberOrdersForEachCustomer GROUP BY [Customer Category] Very often you will need to break the processing into several steps, such as before we created and loaded an array, dis some processing then used a separate UPDATE SET command to fill the column with data and get the job done. This iteration demonstrates that if the job is not too complex you can put the CASE statement together in the SELECT statement. Because this query does not use hard-coded ranges, and therefore is flexible to the dataset, it would intersting to slice this data by region and/or year to see the pattern of customers. Here the 80/20 rule is not in effect, rather the sales are spread out.