Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server
Article

Dynamic 'Sales by Week' Procedure in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.70/5 (24 votes)
23 Sep 20055 min read 139.8K   1.7K   69   25
An approach to creating an aggregate report with a set of dynamic columns based on dates.

Image 1

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:

SQL
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:

SQL
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:

SQL
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.

SQL
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.

SQL
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.

SQL
-- 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:

SQL
 -- 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.

SQL
-- 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.

SQL
-- 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.

SQL
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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
University of Nevada, Las Vegas
United States United States
With a background in education, music, application development, institutional research, data governance, and business intelligence, I work for the University of Nevada, Las Vegas helping to derive useful information from institutional data. It's an old picture, but one of my favorites.

Comments and Discussions

 
QuestionHow to use that Store procedure to Crystal Report Pin
Ricky Desuyo11-Nov-14 15:22
Ricky Desuyo11-Nov-14 15:22 
QuestionThanks Pin
MikeS8-Jun-12 4:25
MikeS8-Jun-12 4:25 
GeneralError in the stored Proc Pin
mitasid115-Aug-10 13:53
mitasid115-Aug-10 13:53 
GeneralRe: Error in the stored Proc Pin
Mike Ellison17-Aug-10 8:35
Mike Ellison17-Aug-10 8:35 
GeneralThis is an awful approach and bad code Pin
--CELKO--11-Mar-09 15:21
--CELKO--11-Mar-09 15:21 
1) Proprietary code
2) Failure to use ISO-8601 formats
3) Procedural programming
4) ISO-11179 data element name violations
5) display formatting in SQL

SQL is a data language and not a computational one. We would build a Calendar table (a century requires only 365245 rows) with the Julianized week number as one of the columns and write very simple queries with a little integer math.
QuestionAlternatives? Pin
Adam Roderick3-Aug-07 5:40
Adam Roderick3-Aug-07 5:40 
AnswerRe: Alternatives? Pin
Mike Ellison3-Aug-07 6:39
Mike Ellison3-Aug-07 6:39 
AnswerRe: Alternatives? Pin
DFelix19-Jun-08 22:42
DFelix19-Jun-08 22:42 
GeneralRe: Alternatives? Pin
Mike Ellison20-Jun-08 5:35
Mike Ellison20-Jun-08 5:35 
GeneralThank you Pin
uv50720-Jul-07 3:26
uv50720-Jul-07 3:26 
GeneralRe: Thank you Pin
Mike Ellison20-Jul-07 5:53
Mike Ellison20-Jul-07 5:53 
QuestionError - related to SQL 2005? Pin
User 102192211-Jul-07 1:42
User 102192211-Jul-07 1:42 
AnswerRe: Error - related to SQL 2005? Pin
Mike Ellison11-Jul-07 5:54
Mike Ellison11-Jul-07 5:54 
GeneralRe: Error - related to SQL 2005? Pin
User 102192211-Jul-07 6:02
User 102192211-Jul-07 6:02 
GeneralRe: Error - related to SQL 2005? Pin
Mike Ellison11-Jul-07 6:19
Mike Ellison11-Jul-07 6:19 
GeneralRe: Error - related to SQL 2005? Pin
User 102192211-Jul-07 23:06
User 102192211-Jul-07 23:06 
GeneralVery Good... Pin
Domingo M. Asuncion25-Mar-07 14:09
Domingo M. Asuncion25-Mar-07 14:09 
GeneralRe: Very Good... Pin
Mike Ellison26-Mar-07 7:53
Mike Ellison26-Mar-07 7:53 
GeneralRe: Very Good... Pin
Domingo M. Asuncion20-May-08 16:19
Domingo M. Asuncion20-May-08 16:19 
GeneralGood Procedure.... one suggestion Pin
jaguirre746521-Jul-06 4:57
jaguirre746521-Jul-06 4:57 
GeneralRe: Good Procedure.... one suggestion Pin
Mike Ellison21-Jul-06 5:51
Mike Ellison21-Jul-06 5:51 
GeneralJust what i needed.. Pin
maxdesmo28-Sep-05 16:39
maxdesmo28-Sep-05 16:39 
GeneralRe: Just what i needed.. Pin
Mike Ellison28-Sep-05 17:54
Mike Ellison28-Sep-05 17:54 
GeneralI can learn from this Pin
S.H.Bouwhuis26-Sep-05 22:19
S.H.Bouwhuis26-Sep-05 22:19 
GeneralRe: I can learn from this Pin
Mike Ellison27-Sep-05 3:20
Mike Ellison27-Sep-05 3:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.