Washington State University



Featherman’s T-SQL Adventures ? - Array Tables (aka SQL Table Variables)Array tables are used to build datasets the RAM of the database. SQL statements build datasets, and array tables free up the developer to merge data from different datasources). Because you can add columns of data together, they can be at different levels of granularity. In SQL, arrays are used similarly to how they are used in other programming environments. Table variables or arrays have been used by programmers for over 50 years to transfer data from storage to working memory to perform data management, data integrations, and data aggregation. RAM has always worked a lot faster than secondary storage. Arrays are used in coding to store values, and as a place where you can manipulate and comingle data and build a structure of values. Arrays tables are used to hold data. and be useful to pull data together when other methodologies do not work.A recurring reporting problem is that data is needed at different levels of granularity, for example it is common to add a final column with totals or % of total metric. In SSRS, PowerBI or Tableau reports you can add column and row totals easily. If your dataset from a SQL query needs a mixture of detailed and summary data then you can use an array to mingle data that was calculated at different levels of summarization. Often several layers of aggregation are needed to compare data sections.Array tables excel at allowing you to pull data together, co-mingling data from different sources, into a compiled dataset that is ready for analysis. Bringing disparate data into one organized dataset allows dashboards and reports to be easily generated and updated. A pre-requisite is that you need to have a dimension attribute (column) in common between the different source data tables. The field in common will usually be a primary key such as employeeID, productID, storeID, but can also be city, state, or country (for example when compiling econometrics data). When you can’t figure a way to pull data together from different sources (i.e., the joins don’t work, and neither does a sub-query), then pull the data into an array table. Some of these examples demonstrated here to explain array tables can also be performed with sub-queries. Sub-queries are deceptively powerful and provide more capability than shown. This author has found situations however, where sub-queries do build the dataset. When using array tables, in essence you are just pulling data from different sources into one table to compact data for storage and further analysis. Again we create a resultset which is a data table that we now might want to save to a database table. For example, array tables can hold data that is culled from different sources, and this array table (aka resultset) can be written to a new permanent SQL database table. Just be sure that the data columns all relate to the same dimension attribute of interest (such as employeeID). Often the compiled data is the datasource for a dashboard, other times the arraytable is the in-memory storage table to hold compiled data in the middle of an Extract, Transform and Load process. The newly compiled database table can then be merged with other data in an iterative ETL process of data culling, formatting and combining. The data organization can be facilitated by running stored procedures and merging datatables together into arrays that are saved to database tables Array tables then are your canvas and you the emerging DBA can paint your masterpiece dataset onto them, column by column.A table variable is literally an array created in memory in the shape of a table (either wide, or tall or tall and wide). You specify the columns, their names and data types. Next you run a SELECT statement that specifies the data for the columns, and to then insert rows of data into the array. To improve data management and simplify organization of data assets, a DBA may want to build fewer more complex resultsets, each one serving as the datasource for many reporting needs (rather than build lots of queries, build a few that are multi-purpose!!). The array table then can be a intermediate step needed to pull together data into a complex dataset (then save the dataset into a new database table.This custom building of datasets will often require the merging of different aggregated data such that the required query results are not possible. In these cases the DBA can use temporary tables to allow the joining of two tables and thereby adding extra columns of aggregated data for either a field name in common (such as month or year) or for product numbers that are in one table but not the other. Examples:USE [Featherman_Analytics];DECLARE @SubCategory nvarchar(30) = 'Mountain Bikes'SELECT [Model], Sum(OrderQuantity) as [Total] FROM FactResellersales as sInner JOIN [dbo].[AW_Products_Flattened] as pON p.ProductKey = s.ProductKeyWHERE [Sub Category] = @SubCategoryGROUP BY [Model]When this query is turned into a stored procedure, the value for the @subCategory variable will not be provided. Rather that value will be passed in when the query is run.For example if the query is saved into a stored procedure called spUnitsForSubCategory then you would run the query and retrieve te data by typingEXEC spUnitsForSubCategory 'Mountain Bikes'107957620000Before we jump into arraytables which is a grid of variables, let’s take another look at declaring and using a single variable in SQL. What is a variable? A variable is a named piece of system memory (in RAM) that is created by, accessible and manipulated by SQL programs and the users of the SQL programs. The DECLARE @variablename datatype is the way to create a variable. You can set the value of the variable immediately or use a SET command later on. The program shown to the left allows one textual sub-category value. Here we show th equery in test mode (by giving a value to the variable). Future usage of the query will receive values (from a dropdown list in an webpage, Excel, SSRS, or Tableau project) passed in and used in the WHERE statement to filter the results retrieved from the database.Next in this document we focus on a special type of variable called a table variable (which is special in that it can have many columns and rows), not just one single parcel of info.But let’s first look at creating a simple nvarchar variable that can accept a word or phrase. Most variables that programmers use can store one value, a number, a date or a word or set of words. In T-SQL use the DECLARE phrase to create a variable in the memory (RAM) of the development machine. The DECLARE line shown to the left creates a variable of datatype nvarchar that can accept up to 30 characters (letters or numbers). The idea is that this query would be ‘called’ or ‘invoked’ from Excel or a reporting software which passes in the value for the variable. This is called a parameterized query and can provide different results depending on value passed into the query, similar to a slicer.We can see that the variable is used in the WHERE clause to filter the results. USE [Featherman_Analytics];SELECT * INTO Featherman_Analytics.dbo.ManagersListFROM [AdventureWorksDW2012].[dbo].[DimEmployee]WHERE [Title] LIKE '%manager%'3272051161150We will use this technique to move some data into your database account. Log into YOUR database, then USE Featherman’s database. Your INTO statement should be YOUR database. You are making a new table so give it a nice name.00We will use this technique to move some data into your database account. Log into YOUR database, then USE Featherman’s database. Your INTO statement should be YOUR database. You are making a new table so give it a nice name.In this module we will also use the concept of copying data from one SQL Server database table into another, or from a database table from a cloudDB to a data table in memory. In this document data is copied from a table or set of tables INTO an array, and used for reporting. You can also perform data management and shaping, comingling data into one array that is saved into a table. You must be the owner of the database to be able create tables in the database to be able to insert/update/delete data. This code runs on the Featherman_Analytics database. The code creates the ManagersList table and inserts 20 rows of data into it from the DimEmployee table of AdventireWorksDW2012.This code creates a new table on a disk drive. In this module we will create virtual tables called arrays and load data into them. 707555710160004153839635000Array Examples: It is common to give each region their own datatables to speed up data management and reporting for that region. Notice on the image here that there are six tables of data, one for each country where sales are made. A common requirement is to need to pull together the tables of data for some further analysis. Hwo would you do that? Copy rows into Excel? No need. Behold the UNION query – which is used to add more rows of data when the schema is the same across all the tables.What we want is the table on the right where you can see the data is merged from the differnet tables (Australia, Canada, France shown).We can use the UNION SELECT command to pull rows of data into an array, then write that array to a new table in the database. This should give you some ideas, you can pull different data together, transform it, add metrics then save the data into a database table, finishing by connecting a report to that table. You can of course automate the entire process using a stored procedure. Let’s dig into the SQL. The first example uses a decimal variable and a table variable.USE [Featherman_Analytics];DECLARE @Sales TABLE ([Country] varchar(50), [Product] varchar(50), [Model] varchar(50), [Totals] decimal(10,2) ) INSERT INTO @Sales ([Country], [Product], [Model], [Totals]) SELECT * FROM [featherman].[BikeSales_Australia]UNION SELECT * FROM [featherman].[BikeSales_Canada]UNION SELECT * FROM [featherman].[BikeSales_France]UNION SELECT * FROM [featherman].[BikeSales_Germany]UNION SELECT * FROM [featherman].[BikeSales_UK]DECLARE @TotalSales decimal = (SELECT SUM([Totals]) FROM @Sales)SELECT [Country], [Model], SUM([Totals]) as [Totals], FORMAT(SUM([Totals])/@TotalSales, 'P2') as [% Total]FROM @SalesGROUP BY [Country], [Model]ORDER BY [Country], [Model]SELECT * INTO [Featherman_Analytics].[dbo].[MyData]FROM @SalesFirst you create a table array, here we have 4 columns of data (Country, Product, Model, Totals). Then we insert into that array, all the data from the BikeSales_Australia.The syntax is INSERT INTO @ARRAYTABLE(columns)SELECT * FROM SQL tableNext you can add more records with the UNION SELECT command. Each additional UNION SELECT command works with the INSERT INTO statement.Next we a value for a % of Total calculation, this is a sub-query of the array table, that computes a grand total.Now that we have all the data in the array and have totalled one column, we need to use a SELECT statement to build the resultset that could be copied into Excel (one day we will have Excel run a stored procedure so the data appears with just a button click.Notice the select statement can add new columns of metrics. We also do not need to select all the columns to include them in the dataset (the product column was omitted).When the data is in an array we can add calculated columns, perform GROUP BY analysis and shape the data into the format that we need depending on granularity of reporting needs. Finally when the data is shaped and formatted, it can be saved back into the database into a new table (which can serve as the datasource of a report or dashboard).USE [Featherman_Analytics];DECLARE @Sales TABLE ([Country] varchar(50), [Product] varchar(50), [Model] varchar(50), [Totals] decimal(10,2), [%Total] decimal(10,2)) DECLARE @TotalSalesA decimal = (SELECT SUM([Product Totals]) FROM [featherman].[BikeSales_Australia])DECLARE @TotalSalesC decimal = (SELECT SUM([Product Totals]) FROM [featherman].[BikeSales_Canada])DECLARE @TotalSalesF decimal = (SELECT SUM([Product Totals]) FROM [featherman].[BikeSales_France])DECLARE @TotalSalesG decimal = (SELECT SUM([Product Totals]) FROM [featherman].[BikeSales_Germany])DECLARE @TotalSalesUK decimal = (SELECT SUM([Product Totals]) FROM [featherman].[BikeSales_UK])INSERT INTO @Sales ([Country], [Product], [Model], [Totals], [%Total]) SELECT [Country], [ProductAlternateKey], [ModelName], [Product Totals] ,[Product Totals]/@TotalSalesAFROM [featherman].[BikeSales_Australia]UNION SELECT [Country], [ProductAlternateKey], [ModelName], [Product Totals] ,[Product Totals]/@TotalSalesC FROM [featherman].[BikeSales_Canada]UNION SELECT [Country], [ProductAlternateKey], [ModelName], [Product Totals] ,[Product Totals]/@TotalSalesF FROM [featherman].[BikeSales_France]UNION SELECT [Country], [ProductAlternateKey], [ModelName], [Product Totals] ,[Product Totals]/@TotalSalesG FROM [featherman].[BikeSales_Germany]UNION SELECT [Country], [ProductAlternateKey], [ModelName], [Product Totals] ,[Product Totals]/@TotalSalesUK FROM [featherman].[BikeSales_UK]SELECT [Country], [Model], SUM([Totals]) as [Totals], SUM([%Total]) as [% Total]FROM @SalesGROUP BY [Country], [Model]ORDER BY [Country], [Model]--SELECT * INTO [Featherman_Analytics].[dbo].[MyData2] FROM @Sales-187747400Oh no the manager did not want to see the % of totals for each model for the entire dataset, they wanted the % of totals by model FOR EACH COUNTRY.How do we provide that data grouping when we haven’t learned window functions yet? Well here’s one way. First we add a new column to the array that will hold a new metric. Next we pull the totals from the 5 tables into 5 variables.Next we run our INSERT INTO SELECT FROM UNION SELECT statements. Each SELECT statement calculates the % of total for that country, for each product in the dataset. We finish with our GROUP BY to change the granularity to Model (removing product) then SUM the totals and % Totals by model. Can you think of an easier way? If the tables were numbered (Country1, etc.) there might be a way to use a loop…. Step 1 - declare the array tables and their columnsStep 2 – Select data using normal queries and load the data INTO the arraysStep 3 Select columns from the two array tables, add metricsStep 4 – join the arrays together on a common field DECLARE @ArrayTable1 Table (column1 int, column2 Decimal)DECLARE @ArrayTable2 Table (column1 int, column2 Decimal)INSERT INTO @ArrayTable1SELECT Column1, Sum(Column2)FROM SomeTable GROUP BY Column1INSERT INTO @ArrayTable2SELECT Column1, Count(Column2) FROM SomeTableGROUP BY Column1SELECT columns and create metrics using columns from either array @ArrayTable1, or @ArrayTable2FROM @ArrayTable1AS T1, @ArrayTable2 AS T2WHERE T1.primarykeyfield = T2primarykeyfield--You can more safely replace the prior two lines that connect the table on a common field with the following FULL JOIN FROM @temptable1 AS T1FULL JOIN @temptable2 AS T2Here is the another way to use arraytables, loading them with data from different data sources, and producing analytics.This query shows one way to combine two tables of retrieved data when you surely have a common field of interest, the primary key. First you create arrays and then SELECT values and INSERT the retrieved data INTO the arrays. Finally you use a final SELECT statement to pull data from the two table variables together and to create new columns of derived data so that the resultset has all the calculated or aggregate fields.The major benefit is that you can put data from different data sources into the same array and then make the columns metrics that you need using data from the different sources, now that it is in your array.Here we just get the results from SSMS compiled into one table, and use it for charts, maps, reports and subsequent analysis. We could also take the merged data columns and save them into a database table or staging table (common when creating ETL procedures). We will see later that this last section where the table variables are joined, and the fields for display are selected from the table variables can vary and provide good solutions.The code above can be used WHEN YOU KNOW FOR SURE that there is an attribute field in common and there are values in the field in both tables (such as month, or city). The WHERE statement is an inner join in that values are displayed ONLY if there is a value in both tables (both side of the join). The blue code shows the way to show the data whether there is a match or not in the other table.This first example is different in that it proves that you can just create an arraytable and load data into it. Sometimes you will want to pull compiled data together from different tables and databases into a compact location even though there is no primary key to connect the tables.USE [Featherman_Analytics];DECLARE @ChannelWidth TABLE([#SubCategories in Stores] int, [#SubCategories on Web] int)--insert the data retrieved below into the specified columns of the arrayINSERT INTO @ChannelWidth ([#SubCategories in Stores], [#SubCategories on Web])SELECT --this is the sub-query for the first column(SELECT(COUNT(DISTINCT(p.[Sub Category]))) FROM [dbo].[FactResellerSales] as rsINNER JOIN [dbo].[AW_Products_Flattened] as p ON p.[ProductKey]= rs.[ProductKey])--this is the sub-query for the second column, (SELECT(COUNT(DISTINCT(p.[Sub Category]))) FROM [dbo].[FactInternetSales] as fisINNER JOIN [dbo].[AW_Products_Flattened] as p ON p.[ProductKey]= fis.[ProductKey])--this is actually retrieving and showing the data SELECT * FROM @ChannelWidthTo simplify the query, the AW_Products_Flattened table is used from Featherman_Analytics Database.It is interesting to know how many product sub-categories are sold on each channel. I guess you can either do this analysis with brute force counting or you can write a SQL Query. Unless you write SQL often, this query might be considered brutal. This query uses sub-queries .Again you define a table variable (a virtual table with two columns) to hold values from different queries, then you use two different SELECT statements that are sub-queries to select (INSERT) values INTO the virtual table.Notice there is no primary key field that is in common between these two tables. We just want to pull values from the tables and display them in a table. Its just a baby step but an important one.You can put any values you want columns of an array, they can be totally different. AS we shall see usually you want to pull data columns about some attribute of interest, so your rows are used to oprganize the data. For example if your first column is US state, you could pull different data into columns for each row (each state).The next is a little more powerful query – Often when you do a join of tables you do so to add more columns of metrics or to make more complete lists. This query tackles a different problem, adding aggregate columns from a second table. One way to do this is to create two virtual tables with the results you want then to join the tables. So here we create the virtual tables, then select data into them, then finally pull the desired compiled data from the two virtual tables. This following query can be written with a fancy subquery, but the calculated fields are MUCH EASIER if you use array tables. And yes 2 array tables are needed.Step 1 – create the arrays and columnsStep 2 – load the data into the arrays766445351155Step 3 – now that the two arrays are loaded with data and joined, we can select the columns that we want from the two arrays, and create some new columns of metricsStep 4 – join the two arraysUSE [AdventureWorksDW2014];DECLARE @Year int = 2013DECLARE @RSSales TABLE ([ProductKey] int, [Reseller Units] int) DECLARE @INETSales TABLE ([ProductKey] int,[Internet Units] int)INSERT INTO @RSSales ([ProductKey],[Reseller Units])SELECT [ProductKey], SUM([OrderQuantity]) FROM [dbo].[FactResellerSales]WHERE DATEPART(year, [OrderDate]) = @YearGROUP BY [ProductKey]INSERT INTO @INETSales ([ProductKey], [Internet Units])SELECT [ProductKey], SUM([OrderQuantity]) FROM [dbo].[FactInternetSales]WHERE DATEPART(year, [OrderDate]) = @Year GROUP BY [ProductKey]--------------------------------------------------------SELECT rs.[ProductKey],rs.[Reseller Units], inet.[Internet Units] , (rs.[Reseller Units] + inet.[Internet Units]) AS [Total Units],format(100* rs.[Reseller Units]/ (rs.[Reseller Units] + inet.[Internet Units] * 1.0), 'N2') AS [% Reseller Channel],format(100* inet.[Internet Units]/ (rs.[Reseller Units] + inet.[Internet Units] * 1.0), 'N2') AS [% Internet Channel]FROM @RSSales rs, @INETSales inetWHERE rs.ProductKey = inet.ProductKeyORDER BY rs.ProductKey58515148751We need to turn this into a stored procedure today, and pass in the year00We need to turn this into a stored procedure today, and pass in the yearHere you can see that the same product key is used for both sales channels. The product key is an internal referent number used as the primary key (rather than the actual model # which is stored in the products table). First declare two virtual tables defining the field names and data types for each virtual table. It’s best to keep the column names short! We will load these columns with data, so that the data type corresponds to the data copied in.In step 2 - Here we select two columns of data from the factResellerSales table and insert the data into the virtual table. There are 266 rows of data copied into the virtual table, each row has two columnsIn step 2 - Here we select two columns of data from the factInternetSales table and insert the data into the virtual table. There are 133 rows of data copied into the virtual table, each row has two columnsHere we select 3 columns of data from the two virtual tables productkey, reseller units, Internet Units, Next we add 3 columns of derived data. Note that an alias for the virtual tables is needed (code in yellow) so that you can refer to the columns directly. NOTE: A CAST() operation can also be used rather than the * 100 trick to make the division work.Clearly the strength of using arraytables is the ease of creating calculated fields from the available columns. If each new metric column was an inner join, then the query would be much harder to write.Programmers challenge!The data could also be sliced by product sub-category in Excel – if you add the columns. Can you do it?USE?[AdventureWorksDW2012];DECLARE?@RSSales?TABLE?([ProductKey]?int,?[Reseller Units]?int)DECLARE?@INETSales?TABLE?([ProductKey]?int,[Internet Units]?int)?INSERT?INTO?@RSSales?([ProductKey],[Reseller Units])SELECT?[ProductKey],?ISNULL(SUM([OrderQuantity]),?0)FROM?[dbo].[FactResellerSales]WHERE?DATEPART(year,?[OrderDate])?=?2007GROUP?BY?[ProductKey]?INSERT?INTO?@INETSales?([ProductKey],?[Internet Units])SELECT?[ProductKey],?ISNULL(SUM([OrderQuantity])?,?0)FROM?[dbo].[FactInternetSales]WHERE?DATEPART(year,?[OrderDate])?=?2007GROUP?BY?[ProductKey]?SELECT?rs.[ProductKey],?rs.[Reseller Units]?AS?[Reseller Units],?ISNULL(inet.[Internet Units],?0)?as?[Internet Units],?rs.[Reseller Units]?+?ISNULL(inet.[Internet Units],?0)?AS?[Total Units]?,FORMAT(CONVERT(DECIMAL(10,4),(rs.[Reseller Units])/(Cast(rs.[Reseller Units]?as?decimal(6,2))?+?ISNULL(inet.[Internet Units],?0))),?'P2')??AS?[% Reseller Channel]?--Thank you William Chilton! -- Yellow code sets one value as decimal -- Green code set the numerator to a decimal-- Grey code – formats the output nicely?,FORMAT(CONVERT(DECIMAL(10,4),(ISNULL(inet.[Internet Units],?0)/(Cast(rs.[Reseller Units]?as?decimal(6,2))?+?ISNULL(inet.[Internet Units],?0)))),?'p2')?AS?[% Internet Channel]?FROM?@RSSales?AS?rsFULL?JOIN?@INETSales?AS?inetON?rs.ProductKey?=?inet.ProductKeyWHERE?rs.ProductKey?IS?NOT?NULLORDER?BY?rs.ProductKey We should be passing in the year as a variable.The prior example used a WHERE clause INNER JOIN which requires that you must have the same key field in both tables. As with any INNER JOIN, you will receive a subset of the total records, if there are records in one table without a corresponding set of values in the other table. This example uses a full join to get all the product sale data from either sales channel (whereas the prior query gets only the products that are sold on both channels). Try running the query with and without the final WHERE filter.As you see in the resultset below, we probably need to use the ISNULL() function to replace the NULLS with 0’s. Can you do it?USE [AdventureWorksDW2012];-- Set up your arrayDECLARE @Sales TABLE ([Product] int, [Reseller Units] int, [Inet Units] int ) -- Declare which columns will be added to INSERT INTO @Sales ([Product], [Reseller Units] )-- Write the SELECT query to get the data for the specified columnsSELECT [ProductKey], SUM([OrderQuantity])FROM [dbo].[FactResellerSales]GROUP BY [ProductKey]-- Choose a column to change the values – here from NULL to a total. Note this is a sub-queryUPDATE @Sales SET [Inet Units] = (SELECT SUM([OrderQuantity]) -- Notice the WHERE statement is a JOIN from the database table to the array table so that the SUM works correctlyFROM [dbo].[FactInternetSales]WHERE [ProductKey] = [Product] )-- Now choose what columns to displaySELECT [Product], [Reseller Units], ISNULL([Inet Units], 0) as [INET Units] FROM @SalesORDER BY [Product]1905000This is an innovative use of the UPDATE TableName SET [Column name] command. Notice we are adding a new column of data with no JOINNotice we gather the values for the UPDATE SET command from a sub-query. This would be normal and nice but what we really need is to update every row in the table adding the Internet sales for every product.In other examples we used two array tables and pulled values from both of them usinga) WHERE rs.ProductKey = inet.ProductKeyOrb) FULL?JOIN?@INETSales?AS?inetON?rs.ProductKey?=?inet.ProductKeyBut look again here we use only one array table, we INSERT 2 columns of data into the arraytable, then use an UPDATE SET to change the NULL value for each row of the 3rd column to a number.The amazing part is that the WHERE statement connects the FactInternetSales column to a column in the arraytable. This opens up a lot of possibilities.We still need to SELECT the values from the arraytable to show them, which gives us a chance to use our ISNULL command (thanks again Andrew Pirie and Leo for the ISNULL code).This code is much simpler than using two tables and a FULL JOIN USE [Featherman_Analytics];DECLARE @Sales TABLE ([Sub Category] nvarchar(50), [Model] nvarchar(50), [ProductID] int, [Reseller Profit] int, [Internet Profit] int)INSERT INTO @Sales ([Sub Category], [Model], [ProductID], [Reseller Profit])SELECT [Sub Category], [Model], p.[ProductKey], SUM([SalesAmount] - [TotalProductCost]) FROM [dbo].[FactResellerSales] as frsINNER JOIN [Featherman_Analytics].[dbo].[AW_Products_Flattened] as p ON frs.ProductKey = p.ProductKeyGROUP BY [Sub Category], [Model], p.[ProductKey], [Product]UPDATE @Sales SET [Internet Profit] = (SELECT SUM([SalesAmount] - [TotalProductCost]) FROM [dbo].[FactInternetSales] as iINNER JOIN [Featherman_Analytics].[dbo].[AW_Products_Flattened] as p ON i.ProductKey = p.ProductKeyWHERE p.[ProductKey] = [ProductID] )SELECT [Sub Category], [Model] , [Reseller Profit], [Internet Profit] , ([Reseller Profit] + [Internet Profit]) as [Total Profit], CASEWHEN [Reseller Profit] > ([Internet Profit] * 1.5) THEN 'Reseller dominant'WHEN [Reseller Profit] > [Internet Profit] THEN 'Reseller favored'WHEN [Internet Profit] > ([Reseller Profit] * 1.5) THEN 'Internet dominant'WHEN [Internet Profit] > [Reseller Profit] THEN 'Internet favored'END as [Channel Preference]FROM @Sales WHERE [Internet Profit] IS NOT NULLORDER BY [Sub Category] , [Model] Here is more in-depth example of using an array to bring together data from different tables without using a FULL JOINWe start by making an array with 5 columns, then in yellow we fill 4 of the columns using a GROUP BY query.Next we give values to the 5th column in blue. We use a sub-query embedded inside a UPDATE SET Command.The interesting factor again is that there is a WHERE statement in the UPDATE SET sub-query. The WHERE statement is an INNER JOIN of the new database table (FactInternetSales) and a column in the array (highlighted green).Notice the gray line of code reinforces the fact that once you have the data you need in your arraytable then you can add more metrics, and a descriptors such as provided by the CASE statement.The data in the arraytable can also be filtered as shown in grey. Another problem solved with table variables – the total sales booked for each sales rep is compared to the sales quota set for that sales rep. This query then is the basis for a dashboard that measures business performance to goal. Combining the data for employee over time is one way to look at the data, another is to look at each time period and rank/rate the sales rep performance within that time period. USE [AdventureWorksDW2012];DECLARE @SalesPerformance TABLE ([EmployeeID] int, [Name] varchar(75), [CalendarYear] int, [CalendarQuarter] int, [SalesAmountQuota] decimal, [SalesAmountActual] decimal)INSERT INTO @SalesPerformance ([EmployeeID], [Name], [CalendarYear], [CalendarQuarter], [SalesAmountQuota] )SELECT q.[EmployeeKey], CONCAT([FirstName], ' ', [LastName]) as Name, [CalendarYear], [CalendarQuarter] , [SalesAmountQuota]FROM [dbo].[FactSalesQuota] as qINNER JOIN [dbo].[DimEmployee] as e ON e.[EmployeeKey] = q.[EmployeeKey]UPDATE @SalesPerformance SET [SalesAmountActual]= (SELECT SUM([SalesAmount]) FROM [dbo].[FactResellerSales]WHERE [EmployeeKey] = [EmployeeID] AND DATEPART(year, [OrderDate]) = [CalendarYear] AND DATEPART(quarter, [OrderDate]) = [CalendarQuarter] GROUP BY [EmployeeKey], DATEPART(year, [OrderDate]) , DATEPART(quarter, [OrderDate]) )SELECT [EmployeeID], [Name], [CalendarYear], [CalendarQuarter], [SalesAmountQuota], [SalesAmountActual], [SalesAmountActual] - [SalesAmountQuota] as [Performance to Goal], FORMAT([SalesAmountActual] / [SalesAmountQuota], 'p1') as [Sales KPI]FROM @SalesPerformanceORDER BY [EmployeeID], [CalendarYear], [CalendarQuarter]/*SELECT * INTO [Featherman_Analytics].[dbo].[SalesPerformance]FROM @SalesPerformance */The sales quotas are in one table and do not have to be aggregated., the actual sales are in another table (and have to be aggregated – notice the GROUP BY) and the employee names are in a third table.That the columns of data came from very different tables is irrelevant, the data can be combined and analyzed.The UPDATE SET command allows you to pull a value produced by a sub-query into a column of the array table.ReferencesOver clause - functions - 3 - Blog - of total - of total with group - ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download