Here’s an interesting problem: create a sales report that accepts a start date and an end date. The output should be a table with one row per salesperson and a column for each month showing the total sales for that person for that month, starting with the month of the start date and ending with the month of the end date.
Let’s use Microsoft’s sample Adventureworks database. If you don’t have it installed already you can download from the CodePlex samples site here:
http://www.codeplex.com/MSFTDBProdSamples/
We can get the information that we need for the report from the table AdventureWorks.Sales.SalesOrderHeader. The table has the following fields that we’ll use for the report:
OrderDate
SalesPersonID
SubTotal
We can get the sales person name from the included view Sales.vSalesPerson.
The first thought might be to use a PIVOT command. There’s even a view included with the database, vSalesPersonSalesByFiscalYears, that displays sales by fiscal year using PIVOT. However PIVOT requires fixed output columns and the requirement is that we show all the months between the start and end date, so we have a variable number of columns depending on the input dates.
The technique that I’m going to describe will output as many monthly columns as you need and will work even in Sql Server 2000, where there be no PIVOT command.
Let’s begin with the SQL that would calculate the total sales by sales person for just one month. I’ll make use of the NextMonth function that I described in a previous posting.
DECLARE @startdate datetime
DECLARE @enddate datetime
SET @startdate = ‘08/01/2003′ — just a random date for which there is data
SET @enddate = dbo.NextMonth(@startdate) -1 — Last day of start month
Select FirstName, LastName, soh.SalesPersonID,
Sum(SubTotal) TotalSales
FROM Sales.SalesOrderHeader soh
JOIN Sales.vSalesPerson sp
ON soh.SalesPersonID = sp.SalesPersonID
WHERE OrderDate BETWEEN @startdate AND @enddate
GROUP BY FirstName, LastName, soh.SalesPersonID
Now lets say that we have an arbitrary start date and end date, such as
SET @startdate = ‘08/01/2003′
SET @enddate = ‘03/01/2004′
We want our query results to sales person information, total sales for the period and the sales for each month within the period:
FirstName
LastName
SalesPersonID
TotalSales
Aug 03
Sep 03
Oct 03
Nov 03
Dec 03
Jan 04
Feb 04
Mar 04
In this example there are eight months, but there could just as easily be one or twenty. The way we can handle any number of months between start and end date is to:
1. First, combine all the sales orders into one row per sales person per month
2. Create a temporary table with the sales person info and a total
3. Loop through each month between start and end date
4. Add a new column for each month
5. Update the new column with the total for each month
6. Display the temp table at the end with all the months
The full source code for the stored proc appears at the end this blog. Most of the steps are straightforward except #4. How do we dynamically add a column for each month? First we need to generate a unique column. We can use the short name of the month plus the year for a unique name for each monthly total:
SET @ColumnName = ‘['+Left(DATENAME(mm,@currDate),3)+' '+Right(Convert(varchar(4), Year(@currDate)),2)+']‘
Now we can dynamically add a column to the table using the EXEC command which dynamically executes sql text. EXEC can be dangerous if the text you execute includes user input because it makes you vulnerable to sql injection attack, but in our case we have complete over the text we are executing. Here’s the command:
EXEC(‘ALTER TABLE ##SalesByMonth ADD ‘ + @ColumnName +’ float’)
This will add a new float column named with the month and year. To populate it with data we’ll need to execute another dynamic statement, updating the new column with the total generated in step one for each sales person for that month.
We call the first temp table #MonthlySalesData and the dynamic output table is #SalesByMonth. Our dynamic update for the newly added column looks like this:
EXEC(
‘UPDATE #SalesByMonth’ +
‘ SET ‘ + @ColumnName + ‘ = MonthlySales’ +
‘ FROM #MonthlySalesData msd’ +
‘ JOIN #SalesByMonth sbm’ +
‘ ON sbm.[SalesPersonID] = msd.[SalesPersonID]‘ +
‘ WHERE OrderMonth = ”’ + @currMonth + ””)
We put this together into a stored proc named SalesPersonSalesByMonth which you can call with a start and end date likes this:
EXEC dbo.SalesPersonSalesByMonth
@startdate = ‘07/01/2003′,
@enddate = ‘09/01/2003′
The output looks like this:
And finally here is the full source of the stored procedure which unfortunately WordPress will totally mangle, making my code unreadable by inserting hundreds of extra line feeds, many of them in the middle of a line and turning every blank line into four blank lines and messing up the font, color and every other aspect of my source. Thank you Word Press.
IFOBJECT_ID('dbo.SalesPersonSalesByMonth', 'P') IS NOT NULLDROP PROCEDURE dbo.SalesPersonSalesByMonth
PROCEDURE dbo.SalesPersonSalesByMonth
@startdateAS DECLARE @currMonth datetime DECLARE @ColumnName varchar(10)
-- Create a temp table for the first staff of the calculations
CREATETABLE #MonthlySalesData (
FirstName
LastName
SalesPersonID
OrderMonth
MonthlySales
)
INSERTINTO #MonthlySalesData
SelectSum(SubTotal) MonthlySalesFROM Sales.SalesOrderHeader sohJOIN Sales.vSalesPerson spON soh.SalesPersonID = sp.SalesPersonID WHERE OrderDate BETWEEN @startdate AND @enddate GROUP BY FirstName, LastName, soh.SalesPersonID, DateAdd(d, -Day(OrderDate)+1, OrderDate)
-- Create initial temp table with sales person info and overall total
CREATETABLE #SalesByMonth (
FirstName
LastName
SalesPersonID
TotalSales
)
INSERTINTO #SalesByMonth
Select
-- Set up the range for the first month
SET@currMonth = @startdate
-- Loop through each month from start to end
WHILE@currMonth <= @enddate
BEGIN
-- Name the column with month and year
SET @ColumnName = '['+Left(DATENAME(mm,@currMonth),3)+' '+Right(Convert(varchar(4), Year(@currMonth)),2)+']'
-- Add the column to the output table
EXEC('ALTER TABLE #SalesByMonth ADD ' + @ColumnName +' float')
-- Replace all nulls with 0s so there will be no nulls in output
EXEC('UPDATE #SalesByMonth SET ' + @ColumnName + ' = 0')
-- Update the column with the sum for each sales person
EXEC('UPDATE #SalesByMonth' +' SET ' + @ColumnName + ' = MonthlySales' +' FROM #MonthlySalesData msd' +' JOIN #SalesByMonth sbm' +' ON sbm.[SalesPersonID] = msd.[SalesPersonID]' +' WHERE OrderMonth = ''' + @currMonth + '''')-- Go to the next monthSET @currMonth = dbo.NextMonth(@currMonth)END SELECT
DROP
DROPGO
TABLE #SalesByMonthTABLE #MonthlySalesData* FROM #SalesByMonth
FirstName, LastName, SalesPersonID, Sum(MonthlySales) TotalSalesFROM #MonthlySalesDataGROUP BY FirstName, LastName, SalesPersonID
-- Now group monthly sales data into one row per salespersonmoneyint,varchar(50),varchar(50),FirstName, LastName, soh.SalesPersonID, DateAdd(d, -Day(OrderDate)+1, OrderDate) OrderMonth,
-- Combine all the sales order rows -- into data by sales person by monthmoneydateTime,int,varchar(50),varchar(50),datetime,
@enddate
)datetime
