What is the most straightforward approach to producing a data set that can be used in a SQL Server Reporting Services report to display the following:
SalesPerson # Sales # Gross Profit
John Doe 100 $140,000 $25,000
Everyone Else (Avg.) 1200 $2,000,000 $250,000
Jane Smith 80 $100,000 $15,000
Everyone Else (Avg.) 1220 $2,040,000 $260,000
...and so on.
First I'll need some helper variables for total counts
/* Few helper variables*/ DECLARE @TotalQuantity int ,@TotalAmount decimal(19, 4) ,@TotalProfit decimal(19, 4) ,@EveryoneElse int
Then we fetch total for everyone in a given period (YEAR = 2009)
/* Fetch totals in the period*/ SELECT @TotalQuantity = sum(SalesQuantity) ,@TotalAmount = sum(SalesAmount) ,@TotalProfit = sum(Profit) ,@EveryoneElse = count(DISTINCT SalesPersonKey) - 1 FROM factSales AS s JOIN dimDate AS d ON s.DateKey = d.DateKey WHERE [Year] = 2009 /* Now we have totals for everyone in the period */
And now for each person vs everyone else, but all in one row.
/* Totals for each sales person vs everyone else Average */ SELECT FullName ,SUM(SalesQuantity) AS [PersonSalesCount] ,SUM(SalesAmount) AS [PersonSalesAmount] ,SUM(Profit) AS [PersonSalesProfit] ,( @TotalQuantity - SUM(SalesQuantity) ) / @EveryoneElse AS [EveryoneElseAvgSalesCount] ,( @TotalAmount - SUM(SalesAmount) ) / @EveryoneElse AS [EveryoneElseAvgSalesAmount] ,( @TotalProfit - SUM(Profit) ) / @EveryoneElse AS [EveryoneElseAvgSalesProfit] FROM factSales AS s JOIN dimDate AS d ON s.DateKey = d.DateKey RIGHT JOIN dimSalesPerson AS p ON p.SalesPersonKey = s.SalesPersonKey WHERE [Year] = 2009 GROUP BY FullName
Now you can package all this in a stored procedure with parameter(s) for date interval. May still need to tweak number of sales people to determine which were active in a certain period and how to count those who did not sell anything. With this,
EveryoneElse means number of sales people who sold something -1; so if you have 10 sales people and only 5 sold something, than
EveryoneElse = 4.