|
USE AdventureWorks
GO
IF EXISTS
(SELECT * FROM sys.objects
WHERE type = 'P'
AND name = 'ProcSalesReportDynamic')
DROP PROCEDURE dbo.ProcSalesReportDynamic
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.ProcSalesReportDynamic (
@StartDate DATETIME,
@NumberOfMonths INT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(MAX);
DECLARE @CurrentMonth INT;
SET @CurrentMonth = 1;
SET @SQL = 'WITH EmployeeSalesByMonth (OrderDate, SalesPersonID, SalesBySalesPersonThisMonth) AS
(
SELECT OrderDate, SalesPersonID, COUNT(SalesPersonID) AS SalesBySalesPersonThisMonth
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID, OrderDate
-- ORDER BY SalesPersonID, OrderDate
)
SELECT * FROM
(SELECT OrderDate, SalesPersonID, COUNT(SalesPersonID) AS SalesBySalesPersonThisMonth
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID, OrderDate
HAVING MIN(OrderDate) = ''' + CAST(@StartDate AS VARCHAR(50)) + '''
--ORDER BY SalesPersonID, OrderDate
) AS ESM1';
WHILE @CurrentMonth < @NumberOfMonths
BEGIN
SET @SQL = @SQL + '
LEFT OUTER JOIN EmployeeSalesByMonth AS ESM'
+ CAST(@CurrentMonth + 1 AS VARCHAR(2))
+ '
ON ESM'
+ CAST(@CurrentMonth AS VARCHAR(2))
+ '.SalesPersonID = ESM'
+ CAST(@CurrentMonth + 1 AS VARCHAR(2))
+ '.SalesPersonID
AND MONTH(ESM'
+ CAST(@CurrentMonth AS VARCHAR(2))
+ '.OrderDate) + 1 = MONTH(ESM'
+ CAST(@CurrentMonth + 1 AS VARCHAR(2))
+ '.OrderDate)
AND YEAR(ESM'
+ CAST(@CurrentMonth AS VARCHAR(2))
+ '.OrderDate) = YEAR(ESM'
+ CAST(@CurrentMonth + 1 AS VARCHAR(2))
+ '.OrderDate)';
SET @CurrentMonth = @CurrentMonth + 1;
END -- End WHILE
SET @SQL = @SQL + '
ORDER BY ESM1.SalesPersonID, ESM1.OrderDate'
--PRINT @SQL;
EXEC (@SQL);
END; --End dbo.ProcSalesReportDynamic
GO
|