Dynamic 'Sales by Week' Procedure in SQL Server

• Download SQL procedure and C# demo project - 19.8 Kb

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.

  -- determine columns to appear in the select clause of the inner detail set
  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:

  -- the from clause of the inner detail set
  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.

  -- the where clause, based on @startingDate and @numWeeks
  set @whereClause = ' where (OrderDate >= ''' 
           + CONVERT(varchar(30), @startingDate, 101) 
           + ''' and OrderDate < ''' 
           + CONVERT(varchar(30), @startingDate + (@numWeeks * 7), 101) 
           + ''')'

  --remember this "inner" detail query
  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.

  -- now we have the detail; create an outer query that aggregates the detail,
  -- grouping by our CategoryName
  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'  

  -- finally, execute the aggregating query
  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.