To achieve the scenarios specification allowing Jay to see the total for each sales rep, we need to either create a new stored procedure or modify an existing one. Looking through the stored procedures available in the Northwind database, I encountered a stored procedure called “Employee Sales by Country” that closely fits the data we need. Let’s base the new report on this stored procedure. We need to alter this stored procedure a little to add the month and year.
View Code Listing 1 to see the altered stored procedure. We added ShipMonth and ShipYear to allow grouping and sorting by that date grouping. This also could be accomplished by adding formulas in the report, or through coding in the unit test. But I prefer to have that data in our stored procedure to make maintenance easier.
Code Listing 1
SET QUOTED_IDENTIFIER ON
GO SET ANSI_NULLS ON
GO ALTER procedure "Employee Sales by Country"
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Employees.Country, Employees.LastName,
Employees.FirstName, Orders.ShippedDate,
Orders.OrderID, "Order Subtotals".Subtotal
AS SaleAmount, month(Orders.ShippedDate)
as 'ShipMonth', year(Orders.ShippedDate) as 'shipyear'
FROM Employees INNER JOIN
(Orders INNER JOIN "Order Subtotals" ON
Orders.OrderID = "OrderSubtotals".OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
order by employees.lastname, employees.firstname, shipyear, shipmonth
GO SET QUOTED_IDENTIFIER OFF
GO SET ANSI_NULLS ON
GO
