USE [Featherman_Analytics]; DECLARE @NumberOrdersForEachCustomer TABLE ([Customer Name] nvarchar(50), CustomerCategory nvarchar(30), [# Orders] int ) /* now that we have an array, we place aggregated values into it, leaving a blank in the CustomerCategory field. */ INSERT INTO @NumberOrdersForEachCustomer ([Customer Name], CustomerCategory, [# Orders]) SELECT [Customer Name], CustomerCategory = ' ', COUNT(DISTINCT([Order ID])) FROM [dbo].[SuperStore_Orders] GROUP BY [Customer Name] /* now go back and use an UPDATE SET command to change the values in the blank CustomerCategory column. */ UPDATE @NumberOrdersForEachCustomer This is another example of adding a new column to an array and placing a value into that is later used to SET CustomerCategory = categorize and slice up the data. This demo is CASE WHEN [# Orders] > 10 THEN 'Top Customer' transitionary to the next content. WHEN [# Orders] BETWEEN 7 AND 10 THEN 'Good Customer' WHEN [# Orders] BETWEEN 4 AND 6 THEN 'Emerging Customer' WHEN [# Orders] BETWEEN 1 AND 3 THEN 'Bottom quartile Customer' Again an array is created, that adds a new column not END in the source data table. The column is created and blanks are placed into the column, using the INSERT /* now show the array and a quick GROUP BY query to summarize the data */ INTO SELECT statements. Then an UPDATE SET command with the CASE WHEN statement is used to SELECT * FROM @NumberOrdersForEachCustomer place values into the column. It does show a clean CASE SELECT CustomerCategory, COUNT(*) as [Count] statement though, values for every row of the FROM @NumberOrdersForEachCustomer CustomerCategory field are populated. GROUP BY CustomerCategory Another INSERT INTO statement could save this data to a permanent table.
USE [Featherman_Analytics]; DECLARE @NumberOrdersForEachCustomer TABLE ([Customer Name] nvarchar(50), CustomerCategory nvarchar(30), [# Orders] int ) /* now that we have an array, we place aggregated values into it, leaving a blank in the CustomerCategory field. */ INSERT INTO @NumberOrdersForEachCustomer ([Customer Name], CustomerCategory, [# Orders]) SELECT [Customer Name], CustomerCategory = ' ', COUNT(DISTINCT([Order ID])) FROM [dbo].[SuperStore_Orders] GROUP BY [Customer Name] /* now go back and use an UPDATE SET command to change the values in the blank CustomerCategory column. */ UPDATE @NumberOrdersForEachCustomer SET CustomerCategory = CASE WHEN [# Orders] > 10 THEN 'Top Customer' WHEN [# Orders] BETWEEN 7 AND 10 THEN 'Good Customer' WHEN [# Orders] BETWEEN 4 AND 6 THEN 'Emerging Customer' WHEN [# Orders] BETWEEN 1 AND 3 THEN 'Bottom quartile Customer' END /* now show the array and a quick GROUP BY query to summarize the data */ SELECT * FROM @NumberOrdersForEachCustomer SELECT CustomerCategory, COUNT(*) as [Count] FROM @NumberOrdersForEachCustomer GROUP BY CustomerCategory This is another example of adding a new column to an array and placing a value into that is later used to categorize and slice up the data. This demo is transitionary to the next content. Again an array is created, that adds a new column not in the source data table. The column is created and blanks are placed into the column, using the INSERT INTO SELECT statements. Then an UPDATE SET command with the CASE WHEN statement is used to place values into the column. It does show a clean CASE statement though, values for every row of the CustomerCategory field are populated. Another INSERT INTO statement could save this data to a permanent table.
Feathermans-TSQL-Analytics-Loops-and-CASE-ETL-Methods-1-18-17.docx