Introduction
Presented in this article is an approach for creating a categorized “Sales by Week” report in SQL Server, in which weeks are represented as columns, categories as rows, with values totaled by those categories. Before describing the stored procedure, where we’ll parameterize this data request and allow for dynamically created columns, it is useful to understand how the general query will work. A detail inner query is used to fit sales numbers under appropriate columns given their position within a particular week. This query is then wrapped in an aggregating outer query that collapses each sales total by its category. For demonstration purposes, Northwind is the target database.
Detail Inner Query
The purpose of the inner query is to provide the table joins we’ll need, and to establish the columns used for each week. The code will work something like this:
SELECT CategoryName
, <<column defining sales data for the first week>>
, <<column defining sales data for the second week>>
, ...
, <<column defining sales data for the last week>>
FROM Orders o
INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
WHERE <<criteria limiting the selection to those dates
between the first and last weeks>>
The FROM clause is simply a join of each table required to get from a Northwind Category name to the sales amounts on individual orders (found in [Order Details]). The WHERE clause will also be straight-forward - a simple date range comparison.
The SELECT clause warrants attention. If we want, for our result set, each week represented as its own column, then we need a separate definition for each within the SELECT clause. Fortunately, they will all follow the same pattern and can be wrapped in a loop in the stored procedure.
To define each column, we'll use a SQL Sever CASE statement. In the case that a given OrderDate falls within the week in question, we’ll output the sales total as a positive value in that week’s column. In the case that the OrderDate does not fall within the week in question, we’ll output a zero value. For example, to define a column for the week of 1 March 1998, we could use the following CASE statement:
CASE
WHEN OrderDate >= '03/01/1998' and OrderDate < '03/08/1998'
THEN (od.Quantity * od.UnitPrice) - od.Discount
ELSE 0
END AS [Week0]
The calculation (od.Quantity * od.UnitPrice) – od.Discount is simply a totaling of the sales amount for that given detail record. In this example, we are applying the alias name [Week0] for the column. When looping this in the stored procedure, we’ll see [Week1], [Week2], etc.
The SQL, for an example of this detail query, reporting three weeks’ worth of sales amounts beginning on 3/1/1998 would then look like the following:
SELECT CategoryName
, CASE
WHEN OrderDate >= '03/01/1998' and OrderDate < '03/08/1998'
THEN (od.Quantity * od.UnitPrice) - od.Discount
ELSE 0
END AS [Week0]
, CASE
WHEN OrderDate >= '03/08/1998' and OrderDate < '03/15/1998'
THEN (od.Quantity * od.UnitPrice) - od.Discount
ELSE 0
END AS [Week1]
, CASE
WHEN OrderDate >= '03/15/1998' and OrderDate < '03/22/1998'
THEN (od.Quantity * od.UnitPrice) - od.Discount
ELSE 0
END AS [Week2]
FROM Orders o
INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
WHERE (OrderDate >= '03/01/1998' AND OrderDate < '03/22/1998')
The data then looks something like this (note the zeros if the given detail record doesn’t fall within those weeks):
CategoryName Week0 Week1 Week2
--------------- ------------ ------------ ------------
Seafood 25.889999 0.0 0.0
Dairy Products 340.0 0.0 0.0
Beverages 1079.75 0.0 0.0
Dairy Products 849.75 0.0 0.0
Confections 418.79999 0.0 0.0
...(etc.) ... ... ...
Condiments 0.0 500.0 0.0
Beverages 0.0 378.0 0.0
Seafood 0.0 249.75 0.0
Grains/Cereals 0.0 71.75 0.0
Seafood 0.0 569.79999 0.0
...(etc.) ... ... ...
Condiments 0.0 0.0 110.0
Dairy Products 0.0 0.0 37.450001
Seafood 0.0 0.0 57.850002
Beverages 0.0 0.0 387.45001
Condiments 0.0 0.0 399.95001
Again, each row here represents a single detail row, with values computed for only a single order item; the category names are duplicated and the values are not aggregated.
Aggregating Outer Query
To achieve the desired format of total sales per category per week, we’ll nest the detail query within an outer query which aggregates the values, grouping by the category name. This outer query is an uncomplicated use of the SUM() function and the GROUP BY clause.
SELECT CategoryName
,Sum(Week0) AS [Week of 1 Mar]
,Sum(Week1) AS [Week of 8 Mar]
,Sum(Week2) AS [Week of 15 Mar]
FROM (
<< the inner query as defined above >>
) AS InnerDetail
GROUP BY CategoryName
Because we applied zero values for those records that do not fall within the given column, the SUM() functions are effectively producing a total only for their respective weeks. The column alias names [Week of xxx] provide a convenient heading to identify each week. The execution of the complete query, again using 1 March 1998 as a starting week, results in the following:
CategoryName Week of 1 Mar Week of 8 Mar Week of 15 Mar
--------------- ------------------- ------------------- -------------------
Beverages 3243.9999694824219 2623.9499816894531 4589.3500213623047
Condiments 2806.0 1669.8500061035156 936.95001220703125
Confections 4932.7000503540039 6142.75 5382.8499145507812
Dairy Products 2136.5499877929687 5157.5 2716.4499931335449
Grains/Cereals 843.65000152587891 1196.75 763.80000686645508
Meat/Poultry 1250.3999862670898 330.0 656.0
Produce 1367.9000244140625 2724.7999877929687 2893.1999969482422
Seafood 2197.7900238037109 3154.2999877929687 2396.1500205993652
Creating the Stored Procedure
The value in constructing a stored procedure from this aggregation query is in parameterization. This kind of query is far more useful if we can pass in a starting date and the number of weeks’ data in which we’re interested. We begin the stored procedure with a number of variable declarations.
CREATE PROCEDURE DynamicCategorySalesByWeek
@startingDate datetime
,@numWeeks int = 4
as
begin
declare @selectClause varchar(8000)
declare @fromClause varchar(8000)
declare @whereClause varchar(8000)
declare @groupByClause varchar(8000)
declare @i int;
declare @sDate datetime
declare @colHead varchar(255)
declare @case varchar(1000)
declare @cases varchar(8000)
declare @sqlInner varchar(8000)
. . .
end
We’ll establish starting date and number of weeks as parameters, using a duration of four weeks as a default. The first portion of the procedure establishes a while loop which defines each week’s column in the inner detail SELECT statement.
set @i = 0
set @cases = ''
while (@i < @numWeeks)
begin
set @sDate = @startingDate + (@i * 7)
set @colHead = '[Week' + CONVERT(varchar(2), @i) + ']'
set @case = ', CASE WHEN OrderDate >= '''
+ CONVERT(varchar(30),@sDate,101)
+ ''' and OrderDate < '''
+ CONVERT(varchar(30), @sDate + 7, 101)
+ ''' THEN (od.Quantity * od.UnitPrice) - od.Discount ELSE 0 END'
set @cases = @cases + '
' +@case + ' as ' + @colHead
set @i = @i + 1
end
set @selectClause = 'select CategoryName' + @cases
In addition to defining the CASE statement for each column using a calculation based on the @startingDate parameter and the loop counter, this code also creates our column headings of [Week0], [Week1], [Week2], etc. The FROM clause for the inner detail set follows in the code:
set @fromClause = '
from Orders o Inner Join [Order Details] od on o.OrderID = od.OrderID
Inner Join Products p on od.ProductID = p.ProductID
Inner Join Categories c on p.CategoryID = c.CategoryID
'
The carriage returns embedded in the @fromClause string are purely for debug readability (one may embed print statements to test the SQL being compiled). The WHERE clause then applies the appropriate date range given @startingDate and @numWeeks.
set @whereClause = ' where (OrderDate >= '''
+ CONVERT(varchar(30), @startingDate, 101)
+ ''' and OrderDate < '''
+ CONVERT(varchar(30), @startingDate + (@numWeeks * 7), 101)
+ ''')'
set @sqlInner = @selectClause + @fromClause + @whereClause
To create the outer, aggregating query, we use another while loop to apply a SUM() to each of the [WeekX] columns. We’ll also define a friendly title for each column.
set @i = 0;
set @cases = ''
while (@i < @numWeeks)
begin
set @sDate = @startingDate + (@i * 7)
set @colHead = '[Week of ' + CONVERT(varchar(255), @sDate, 6) + ']'
set @case = ', Sum([Week' + CONVERT(varchar(2), @i) + '])'
set @cases = @cases + '
' +@case + ' as ' + @colHead
set @i = @i + 1
end
Finally, we compile the complete aggregation query (wrapping the inner detail query) and execute it.
set @selectClause = 'select CategoryName as Category' + @cases
set @fromClause = ' from (' + @sqlInner + ') z'
set @groupByClause = ' group by CategoryName order by CategoryName'
execute(@selectClause + @fromClause + @groupByClause)
The complete stored procedure may be downloaded by clicking the link at the top of this article.
About the Demo Project
The demo project is a simple ASP.NET application that consumes the dynamically constructed query. The page default.aspx contains a very simple DataGrid control, with only code necessary to retrieve the data source from the stored procedure and apply presentation formatting. To use the demo project, execute the file DynamicCategorySalesByWeek.sql within the Northwind database, establish EXECUTE permissions on it for the ASP.NET user, and modify web.config to contain the appropriate connection string.
Summary
The DynamicCategorySalesByWeek stored procedure presented in this article demonstrates an approach to creating a summative report with a set of dynamic columns based on dates. In this case, we chose weeks for our columns, but the technique could be adapted easily for months, quarters, or years. The procedure constructs an aggregation outer query which wraps an inner detail query, using while loops to assemble columns defined with CASE statements. The CASE statements ensure that only values from detail records within the week are summed within that week’s column. By encapsulating these statements within a dynamic, parameterized procedure, the developer gains flexibility with this data request and can incorporate that flexibility in an application interface.
