Washington State University



Featherman’s Advanced T-SQL Analytics – Window Functions? 10-20-19This document reviews T-SQL Window functions that are an option to be used when creating columns of analytics. The innovation is that the metric is claculated within sub-groupings of data (groups of records) called a window. Window functions therefore have nothing to do with Microsoft Windows, rather Window Functions are T-SQL syntax that are used when creating certain types of metrics that examine a group of data (such as a running total by week or month). Window functions are an option when creating columns of metrics to place records within groups, and determine how the calculations are performed within the groups. For example the invoices of a sorted customer list can be displayed with a running total, and when the next customer is shown, a new window is created and the running total starts again for that customer.To date the calculations that were performed were for one group or several groups defined by a GROUP BY. There were no running totals, or comparison of time periods. This document demonstrates some uses of the OVER() PARTITION BY() syntax to build windows (groups of data) and add new types of analytics that we have not used to date. Window functions are to perform calculations within a group of rows. The unique feature is that the metric will restart its calculation for every new group (i.e. product subcategory within product category). For example a moving average should start over when the product subcategory changes from road bikes to mountain bikes.Window functions are used as part of an extract, transform and load data process, extracting data from database tables, changing it and loading the compiled resultset into the data warehouse. Just don’t do these calculations in a query that is inside a report or use the query builder inside a report. The concern there is that you have to re-create the query inside the next report as well. Powerful derived fields as shown here should be discussed by the DBA’s, analysts and management team and institutionalized as IT assets (not buried in a random report that an analyst creates and doesn’t document). From Postgre: “A?window function?performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.”So this document introduces the participant to ‘window functions’ of SQL. There actually isn’t a specific T-SQL syntax ‘Window’ rather the OVER() clause is used to define a window (a sub-grouping) of records within which to perform analytics on a group. We are consolidating 60,000 rows with the GROUP BY and then for the new metric column controlling how the calculation is performed breaking the rows into groups called windows.Begin by copying & pasting the base query below into a new SQL Server query to make a base table. We will add columns of derived data analytics to this base query. Examine the result set and envision the analytics that can be run from it. For each added calculated field try to envision another problem context that could be solved with this new functionality.Base Query – Please copy this base query syntax into a new query in SSMS and run it. We will add several calculated columns next.You can check the appendix to see an explanation of the base query.USE [AdventureWorksDW2012];SELECT [EnglishProductSubcategoryName] AS [SubCategory], YEAR(i.[OrderDate]) AS [Year], DATEPART(MONTH, i.OrderDate) AS [Month#], DATENAME(MONTH, i.OrderDate) AS [MonthName], COUNT(i.[OrderQuantity]) AS [Monthly Qty], SUM(i.[SalesAmount])AS [Monthly Revenue], SUM([SalesAmount] - [TotalProductCost]) as [Profit]FROM [dbo].[FactInternetSales] as iINNER JOIN [dbo].[DimProduct] as p ON p.[Productkey] = i.[Productkey]INNER JOIN [dbo].[DimProductSubcategory] as s ON s.[ProductSubcategoryKey]= p.[ProductSubcategoryKey]INNER JOIN [dbo].[DimProductcategory] as c ON c.[ProductCategoryKey]= s.[ProductCategoryKey]WHERE i.[OrderDate] BETWEEN '1/1/2006' and '12/31/2007' AND [EnglishProductCategoryName] = 'Bikes'GROUP BY [EnglishProductSubcategoryName], YEAR(i.[OrderDate]), DATEPART(MONTH, i.OrderDate) , DATENAME(MONTH, i.OrderDate) ORDER BY [EnglishProductSubcategoryName] ,YEAR(i.[OrderDate]) , DATEPART(MONTH, i.OrderDate) Notice there are different Product Subcategories and the sales are lined up by month within the sub-categories.If you used this base query to create a view and called the view from Excel, then you could use a pivot table to cross analyze the sales by region to investigate whether sales are regional (ie. does Asia buy a lot of Bikes in January and America buys a lot of Bikes in June?). Armed with this information the brand manager can plan a promotional campaign or a ‘special edition’ product. (or at least make more efficient bulk shipping decisions to pre-place the products in the correct regions to handle the peak sales times. )FYI – If you added the productalternatekey, and the englishproductname fields to the SELECT and GROUP BY statements, then you would be able to analyze the profitability of certain products or product lines in the product line. You probably would want to reduce the dataset to filter down to just one sub-category at a time. In this way you can make decisions whether to expand or contract the product line.In the code below compare the fields in the partition by and the ORDER BY portion of the window function – they are working together with the dimensions in the base query’s GROUP BY statement.The OVER (PARTITION BY [], ORDER BY) statement define how the measure will be calculated. In the first example the running total is calculated by subcategory and year. So within each year the running total restarts. When the data switches to the next sub-category the running total also restarts.HOWEVER look at the GROUP BY() clause to notice the DATEPART(MONTH, i.OrderDate) field is in there, so the data is compiled by month. IT wouldn’t make sense to restart the running total by month, so it is not included in the PARTITION BY () claysePortions quoted from Function #1 - Running Totals - Add this code before the FROM statement of the base query to add a running total. Running totals are great if you want to create a combo chart as shown below. You may say hey, I don’t need this difficult content, I can create charts from downloaded data any time I want. Sure says the DBA, but can you automate the process? If we create the SQL queries and then call them from SSRS or Excel then the entire reporting or dashboard process can be automated, which means no employee ever needs to hand-make the charts again. DBA’s can also set the data to refresh for a report or spreadsheet at pre-determined time intervals.-- this is a running total that resets for each year within a SubCategory,FORMAT(SUM(SUM(SalesAmount) ) OVER(PARTITION BY [EnglishProductSubcategoryName], YEAR(OrderDate)ORDER BY DATEPART(MONTH, OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 'N0') AS [Running Sales Total for Year]-- this is a running total that resets for each SubCategory ,FORMAT(SUM(SUM(SalesAmount) ) OVER(PARTITION BY [EnglishProductSubcategoryName]ORDER BY YEAR(OrderDate), DATEPART(MONTH, OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) , 'N0')AS [Running Sales Total to date]This first window function creates a calculated field that has a running total that restarts every year and every product subcategory. So the Partition By has 2 columnsNotice the difference in the second window function and the derived calculations from that. It is a similar running total but it only resets per product subcategory not per year. So the partition by is performed using only 1 column. Since there is more data in the running total and the same month numbers can repeat we need to add year to the ORDER BY phrase Note: The chart is a combination chart, a secondary axis should be added to it.Should we try to move the data to Excel and create a combo chart? Should we convert the data to weekly totals?544992924389500693052818200-48729209458900Line by line explanation brought to you by Featherman:,SUM(SUM([SalesAmount])) The first sum indicates that we are summing values – the running total over the window.The second sum is used since inside each grouping partition (each window of data) we are consolidating data using a GROUP BY query. We are consolidating 60,000 rows with the GROUP BY and then further breaking the rows into groups called windows for the new metric column.OVERThe OVER () clause includes the PARTITION BY and the ORDER BY statements. They are broken out seperately here for clarityThe OVER term defines the window of data which could be a year of data, a category of data, or combination. The term window refers to a set of rows for which you want to calculate an analytic, typically a running total, % of total, or comparison of the current row (e.g., month) from the prior row (e.g. prior month). To include all the rows of the dataset in the window then leave the term blank OVER() as we did before when using NTILE()OVER The OVER “Determines the partitioning and ordering of a rowset before the associated window function [the sum or count or other calculation] is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.”OVER starts the specification of the partition which is the category (the field) that is being used to confine the running total. When do you reset the running total back to zero? In the two examples, when the sub-category changes, or the year within a sub-category changes. (PARTITION BY [EnglishProductSubcategoryName], YEAR(OrderDate)PARTITION BY subdivides the window into partitions which can be based on one or more columns. This is where understanding granularity of data is critical. PARTITION BY “Divides the query result set into partitions [groups of rows]. The window function (the moving average etc.) is applied to each partition separately and computation restarts for each partition.”ORDER BY DATEPART(MONTH, OrderDate)Specifies the sorting within the windowRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)The Range specifies what rows to total, here total all the preceding rows in the partition and add to it the value in the current row.AS [Running Total]This entire code block was used to create a calculated column called running total. Was it worth it? Well, many different reports can be derived from this query, and you did not have to do the work manually in Excel.Window Functions – Running Totals Example #2USE [AdventureWorksDW2012];SELECT [EnglishProductSubcategoryName] AS [SubCategory], DATEPART(MONTH, i.OrderDate) AS [Month#], DATENAME(MONTH, i.OrderDate) AS [Month], COUNT(i.[OrderQuantity]) AS [Monthly Units], FORMAT(SUM(i.[SalesAmount]), 'N0') AS [Monthly Revenue],FORMAT(SUM(SUM([SalesAmount])) OVER (PARTITION BY [EnglishProductSubcategoryName] ORDER BY DATEPART(MONTH, i.OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 'N0') AS [Revenue Running Total], FORMAT(SUM([SalesAmount] - [TotalProductCost]), 'N0') as [Profit],FORMAT(SUM(SUM([SalesAmount] - [TotalProductCost])) OVER (PARTITION BY [EnglishProductSubcategoryName] ORDER BY DATEPART(MONTH, i.OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 'N0') AS [Profit Running Total]FROM [dbo].[FactInternetSales] as iINNER JOIN [dbo].[DimProduct] as p ON p.[Productkey] = i.[Productkey]INNER JOIN [dbo].[DimProductSubcategory] as s ON s.[ProductSubcategoryKey]= p.[ProductSubcategoryKey]INNER JOIN [dbo].[DimProductcategory] as c ON c.[ProductCategoryKey]= s.[ProductCategoryKey]WHERE YEAR(i.[OrderDate]) = 2008 AND c.ProductCategoryKey = 1GROUP BY [EnglishProductSubcategoryName], DATEPART(MONTH, i.OrderDate), DATENAME(MONTH, i.OrderDate) ORDER BY [Subcategory], DATEPART(MONTH, i.OrderDate) 356870733425This pivot chart needs a slicer!00This pivot chart needs a slicer!This example differes in that the focus is not on comparing all months in the dataset (the prior dataset is 24 rows), and looks at months combined together (the dataset is 12 months)-1079535788600023495279146000/* Here a running total of the [SalesAmount] column is totaled for each month in each product subcategory group. Notice the running total resets for each change in ProductSubcategory. The fields in the partition by are some (not necessarily all) the fields in the GROUP BY */OVER determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.So this is just a running total by sub-category. Since the rows of data are by month, there are many rows of data that are in the running total.Window Functions – Running Totals Example #3USE [AdventureWorksDW2012];SELECT [EnglishCountryRegionName] as [Country], [EnglishProductSubcategoryName] AS [SubCategory], DATEPART(MONTH, rs.OrderDate) AS [Month#], DATENAME(MONTH, rs.OrderDate) AS [Month], COUNT(rs.[OrderQuantity]) AS [Monthly Units], FORMAT(SUM(rs.[SalesAmount]), 'N0') AS [Monthly Revenue]/* Here a running total of the [SalesAmount] column is totaled for each month in each product subcategory group. Notice the running total resets for each change in ProductSubcategory. The fields in the partition by are some (not necessarily all) the fields in the GROUP BY */,FORMAT(SUM(SUM([SalesAmount])) OVER (PARTITION BY [EnglishCountryRegionName], [EnglishProductSubcategoryName]ORDER BY DATEPART(MONTH, rs.OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 'N0')AS [Revenue Running Total], FORMAT(SUM([SalesAmount] - [TotalProductCost]), 'N0') as [Profit],FORMAT(SUM(SUM([SalesAmount] - [TotalProductCost])) OVER (PARTITION BY [EnglishCountryRegionName], [EnglishProductSubcategoryName]ORDER BY DATEPART(MONTH, rs.OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 'N0')AS [Profit Running Total]FROM [dbo].[FactResellerSales]as rsINNER JOIN [dbo].[DimReseller] as r ON r.ResellerKey = rs.ResellerKeyINNER JOIN [dbo].[DimGeography] as g ON g.[GeographyKey] = r.GeographyKeyINNER JOIN [dbo].[DimProduct] as p ON p.[Productkey] = rs.[Productkey]INNER JOIN [dbo].[DimProductSubcategory] as s ON s.[ProductSubcategoryKey]= p.[ProductSubcategoryKey]INNER JOIN [dbo].[DimProductcategory] as c ON c.[ProductCategoryKey]= s.[ProductCategoryKey]WHERE c.ProductCategoryKey = 1GROUP BY [EnglishCountryRegionName],[EnglishProductSubcategoryName], DATEPART(MONTH, rs.OrderDate) , DATENAME(MONTH, rs.OrderDate) ORDER BY [Country], [SubCategory], DATEPART(MONTH, rs.OrderDate) This query adds region which helps to really dig into the analysis. You can use region as a slicer.Also with a focus on profit it is very interesting to see that sales are often pushed at the expense of profit.Sure it’s good to keep the factory working, but if we are losing money in some regions, it’s time to take a closer look, and make some changes.What are the reasons to have good sales amounts yet lose money?139702517775242570365125Go ahead and replicate some of these charts in Excel using a pivot chart.Window Function #2 - Moving average - Add this code before the FROM statement of the base query to add a calculated column that displays a moving average. Moving averages are used to smooth out temporary fluctuations in values. As a manager (even of your own retirement account), rather than ‘knee-jerk’ react to temporary dips in performance, it may be prudent to step back and keep partially focused on a more summary trend. Moving averages smooth out the peak and valley fluctuations in the data. Sales people love this reporting tool so that they don’t get hammered often. You can also add a moving average to excel line charts.-- This is a 3 month moving average, so in the ROWS statement, the current row plus 2 preceding = 3 rows total which is a three month moving average.,AVG(SUM(SalesAmount)) OVER (PARTITION BY ([EnglishProductSubcategoryName]) ORDER BY DATEPART(MONTH,OrderDate)ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)AS [Revenue 3 Month Avg.]Line by line explanation:, AVG(SUM(SalesAmount)) OVER The AVG syntax delineates that we will calculate an average of the summed records over the partition window. The Sum(fieldname) is needed as we are consolidating 60,000 rows into groups based on the month.) So we are averaging the monthly sums and starting over each new sub-category.(PARTITION BY ([EnglishProductSubcategoryName]) Break the windows by Subcategory (this specifies the groups of records for which the three month moving average is calculated.ORDER BY DATEPART(MONTH,OrderDate)Within each window, perform the calculation by row. Each row is a month (set here, and in the group by statement below)ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS [Three Month Avg. Profit]Rather than a RANGE statement, we need the more specific ROWS statement. This is where you specify if you want a 3 or 4 month moving average or other calculation.-- This is a 3 month moving average, the current row plus 2 preceding = 3 rows total.Window Function #3 - Side by Side Comparison of Current and Prior RecordAdd this code after your syntax before the FROM statement that adds a calculated field to show the prior month’s data for comparison purposes. Accesses data from a previous row in the same result set. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row. This is a precursor to creating a KPI that compares the current month’s sales to the prior. For example showing a % change each month makes a nice chart! --prior month revenue, LAG(SUM(SalesAmount), 1, NULL) OVER (PARTITION BY (EnglishProductSubcategoryName) ORDER BY YEAR(OrderDate), DATEPART(MONTH, OrderDate) ) AS [PriorMonth Revenue] 62230827552Line by line explanation:, LAG(SUM(SalesAmount), 1, NULL)1 is the offset value (This is used to count the number of rows back from the current row to obtain the value – here we count 1 row back).If no value is found (outside the partition) then return NULLOVER (PARTITION BY (EnglishProductSubcategoryName)OVER determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.PARTITION BY clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. ORDER BY YEAR(OrderDate), DATEPART(MONTH, OrderDate) ) AS [PriorMonth Revenue]ORDER BY clause determines the fields that are used for grouping for the row totals In class?? Do a 12 month comparison (year over year analysis by subcategory by region. Sounds like part of an assignment!Window Function #4 - Numeric Change from Prior Period – You can run this queryUSE [AdventureWorksDW2012];SELECT [EnglishProductSubcategoryName] AS [SubCategory], DATEPART(MONTH, rs.OrderDate) AS [Month#], DATENAME(MONTH, rs.OrderDate) AS [Month], COUNT(rs.[OrderQuantity]) AS [Monthly Units], FORMAT(SUM(rs.[SalesAmount]), 'N0') AS [Monthly Revenue]--prior month revenue, FORMAT(LAG(SUM(SalesAmount), 1, NULL) OVER (PARTITION BY (EnglishProductSubcategoryName) ORDER BY DATEPART(MONTH, OrderDate) ) , 'N0')AS [PriorMonth Revenue]--comparison to prior month revenue, FORMAT(SUM(rs.[SalesAmount]) - LAG(SUM(rs.[SalesAmount]), 1, NULL)OVER (PARTITION BY (EnglishProductSubcategoryName)ORDER BY DATEPART(MONTH, OrderDate)), 'N0') AS [Month Over Month]FROM [dbo].[FactResellerSales]as rsINNER JOIN [dbo].[DimReseller] as r ON r.ResellerKey = rs.ResellerKeyINNER JOIN [dbo].[DimGeography] as g ON g.[GeographyKey] = r.GeographyKeyINNER JOIN [dbo].[DimProduct] as p ON p.[Productkey] = rs.[Productkey]INNER JOIN [dbo].[DimProductSubcategory] as s ON s.[ProductSubcategoryKey]= p.[ProductSubcategoryKey]INNER JOIN [dbo].[DimProductcategory] as c ON c.[ProductCategoryKey]= s.[ProductCategoryKey]WHERE c.ProductCategoryKey = 1GROUP BY [EnglishProductSubcategoryName], DATEPART(MONTH, rs.OrderDate) , DATENAME(MONTH, rs.OrderDate) ORDER BY [SubCategory], DATEPART(MONTH, rs.OrderDate) , SUM(SalesAmount) - LAG(SUM(SalesAmount), 1, NULL) OVER (PARTITION BY (EnglishProductSubcategoryName) ORDER BY YEAR(OrderDate), DATEPART(MONTH, OrderDate)) AS [Month Over Month]115570407479500229870214122000Line by line explanation:, SUM(SalesAmount) - LAG(SUM(SalesAmount), 1, NULL)For each row take the sum of the sales amount (grouped up by the fields specified in the GROUP BY statement) and subtract out the sum of sales for the prior period (Lag 1 month).OVER (PARTITION BY (EnglishProductSubcategoryName)Perform this subtraction within a grouping (aka a window into the data) of data here within a set of rows formed by the partition EnglishProductSubcategoryName. This means start the calculations over if the row is the first row of a sub-category.ORDER BY YEAR(OrderDate), DATEPART(MONTH, OrderDate))AS [Delta Revenue]ORDER BY clause determines grouping level for the calculations. The rows are totals for year and monthWindow Function #5 - Month Over Month Variance % Change - Add this code after the prior syntax to show the % variance change in sales as compared to the prior month. This column is perfect data sources for an SSRS or Excel-based KPI with conditional formatting. You could similarly write a year over year analysis or week over week., (SUM(SalesAmount) - LAG(SUM(SalesAmount), 1, NULL) OVER(PARTITION BY (EnglishProductSubcategoryName) ORDER BY EnglishProductSubcategoryName) ) / LAG(SUM(SalesAmount), 1, NULL) OVER (PARTITION BY (EnglishProductSubcategoryName) ORDER BY EnglishProductSubcategoryName) AS [Percent Variance]52704380492043180198056500Line by line explanation:, (SUM(SalesAmount) - LAG(SUM(SalesAmount), 1, NULL)The full formula results are formatted as a percent using the FORMAT() syntax. Here the sum that was calculated for the row – based on the GROUP BY conditions is calculated then the sum for the prior row (the prior month in the window partition is subtracted from that value. Use of the format function is not always desirable. If you are needing to do a lot of work in Excel using your resultset its best to do the formatting in Excel, not in SQL.OVER(PARTITION BY (EnglishProductSubcategoryName) ORDER BY EnglishProductSubcategoryName) ) / LAG(SUM(SalesAmount), 1, NULL) OVER (PARTITION BY (EnglishProductSubcategoryName) ORDER BY EnglishProductSubcategoryName)The numerator is then divided by the total for the prior month, resulting in a percentage.--% variance from prior month = (Current month value- Previous month value)/Previous month value ) AS [Percent Variance]The FORMAT() statement is completed and the column is given a name.Note: When this query is run using Excel’s MSFT query, there is no problem with data types. However if the query is saved into a stored procedure, and called from SSRS, or used as the base query to load a powerpivot database in excel, the FORMAT() operation actually turns the column into a text column. This is bad. This error intentionally left in as a teaching case. So do your formatting in Excel not in SQL (unless you are doing a quick analysis and copying/pasting directly into Excel.) If you are building a query that will be used repeatedly as the base query for a report or PowerPivot import, then do not use FORMAT().88341207613658601710766445Window Function #6 - Breaking records into equal sized groups. You can use NTILE() to quickly break your records into groups of roughly equal size. For example an NTILE(5) would create five ‘bins’ each of which would hold roughly 20% of the records. You can place words in each row as shown in the following example or you can perhaps place a number in the cell. It is very easy for your reporting software such as Excel or SSRS to read the number in the cell and render it as an indicant (green, yellow, red stop light graphic for example). While we looked at this SQL before, it is included here as it should make more sense now, CASE NTILE(4) OVER (PARTITION BY ([EnglishProductSubcategoryName])ORDER BY SUM(SalesAmount) DESC)WHEN 1 THEN 'Gangbusters'WHEN 2 THEN 'Great Month'WHEN 3 THEN 'Good Month'WHEN 4 THEN 'Slow Month'END AS [Sales Rating]Line by line explanation:, CASE NTILE(4) OVER (PARTITION BY ([EnglishProductSubcategoryName])This line specifies that the data is being broken into 4 categories. You can choose as many categories as you want. Four or three are good, if you will use the column as the datasource for a KPI indicant.ORDER BY SUM(SalesAmount) DESC)This line specifies that the records will be summed according to the GROUP BY expression criteria. The SUM(fieldname) totals the records based on the SalesAmount column values and sorts them in descending order - highest to lowest.WHEN 1 THEN 'Gangbusters'WHEN 2 THEN 'Great Month'WHEN 3 THEN 'Good Month'WHEN 4 THEN 'Slow Month'END This is similar to a select case. We are writing words into each row in the column. When the value placed in a cell is 1, then the record is in the bin (group) with the highest value (top 25%). Other values are written to the cell based on the other lower rankings. AS [Sales Rating]Portions quoted from you need a review of the query syntax – here is an explanation of base querySELECT [EnglishProductSubcategoryName] AS [SubCategory], YEAR(i.[OrderDate]) AS [Year]We will group up sales performance analytics based on the product category, sub-category, product, year and month, DATEPART(MONTH, i.OrderDate) AS [Month#]Use DATEPART to extract out the Month #. There are many other usages of DATEPART, DATENAME(MONTH, i.OrderDate) AS [MonthName]Use DATENAME to extract out the textual Month name (i.e., January)There are many other usages of DATENAME, COUNT(i.[OrderQuantity]) AS [Monthly Qty], , SUM(i.[SalesAmount]), 'N0') AS [Monthly Revenue], FORMAT(SUM([SalesAmount] - [TotalProductCost]) as [Profit]This will count up the order quantity column, based on the defined grouping field(s).Line 2 will total up the sales amount column based on the defined grouping field(s).Line 3 calculate the profit generated based on the defined grouping field(s).Unlike a pivot table’s calculated field, in SQL you have to define the grouping criteria (columns) before you run the queryFROM [dbo].[FactInternetSales] as iINNER JOIN [dbo].[DimProduct] as p ON p.[Productkey] = i.[Productkey]INNER JOIN [dbo].[DimProductSubcategory] as s ON s.[ProductSubcategoryKey]= p.[ProductSubcategoryKey]INNER JOIN [dbo].[DimProductcategory] as c ON c.[ProductCategoryKey]= s.[ProductCategoryKey]Here the tables that hold the fields for analysis are identified and lined together using the INNER JOIN termWHERE i.[OrderDate] BETWEEN '1/1/2006' and '12/31/2007'AND [EnglishProductCategoryName] = 'Bikes'Here we filter down the dataset’s date range. Show all the bikes data for the Internet sales channel.GROUP BY [EnglishProductCategoryName], [EnglishProductSubcategoryName], YEAR(i.[OrderDate]) , DATEPART(MONTH, i.OrderDate) , DATENAME(MONTH, i.OrderDate) Here we are grouping sales on sub-category, product, year and month. This we can see row totals for each product for each month in the 12 month time period specified. There is a SQL rule that all non-aggregate fields (ie SUM(), etc) that are specified in the SELECT statement MUST be specified in the GROUP BY statement. Notice we cannot use alias’ in the GROUP BY statementORDER BY [EnglishProductSubcategoryName], YEAR(i.[OrderDate]), DATEPART(MONTH, i.OrderDate) This is the sorting ................
................

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

Google Online Preview   Download