I'd have probably put the details in a separate table so I could just truncate the table for reuse if I needed to.

The rest took a bit of time to type but it's pretty easy if you follow the same steps that I laid out in the article. Of course, comments are in the code. Let me know if you have any questions. And thanks for the great test code setup you did. It made it a whole lot easier to help you in may areas without me having to ask a single question. Very well done.

CREATE PROCEDURE dbo.InvoiceReport

/**********************************************************************************************************************

Purpose:

Given a valid start and end date, aggregate and pivot invoice details by whole month.

Usage Example:

EXEC dbo.InvoiceReport 'Jun 2012','Feb 2013' --Debug mode is OFF. Report is displayed if no errors.

EXEC dbo.InvoiceReport 'Jun 2012','Feb 2013', 1 --Debug mode is ON. The Dynamic SQL is displayed.

Revision History:

Rev 00 - 11 Apr 2013 - Jeff Moden

- Initial creation and unit test.

**********************************************************************************************************************/

--===== Declare the parameters for this stored procedure

@pStartMonth DATETIME

, @pEndMonth DATETIME

, @pDebug BIT = 0

AS

--===== Suppress the auto-display of rowcounts to prevent the rowcounts from causing false errors if a GUI is used

-- and to cleanup the display if a GUI is not used.

SET NOCOUNT ON

;

--===== Local Variables

DECLARE @SQL VARCHAR(MAX)

;

--===== Create the static part of the SELECT list

SELECT @SQL = '

SELECT [Date] = s.InvDate

, [Invoice #] = s.InvNo

, [Client] = s.Client

, [Amount] = s.Amount'

;

--===== Create the dynamic part of the SELECT list

WITH

cteSequenceMonth AS

( --=== This creates a sequence of month dates based on the input parameters

SELECT SequenceMonth = DATEADD(mm,t.N-1,@pStartMonth),

t.N

FROM dbo.Tally t

WHERE t.N <= DATEDIFF(mm,@pStartMonth,@pEndMonth)+1

),

cteMonth AS

( --=== Create the display and comparison month strings for replacement values in the dynamic SQL.

SELECT DisplayMonth = QUOTENAME(REPLACE(SUBSTRING(CONVERT(VARCHAR(15),SequenceMonth,6),4,15),' ','-'))

, CharMonth = CONVERT(CHAR(6),DATEPART(yy,SequenceMonth)*100 + DATEPART(mm,SequenceMonth))

, N

FROM cteSequenceMonth

) --=== Create the dynamic pivot SQL

SELECT @SQL = @SQL

+ REPLACE(

REPLACE('

, <<DisplayMonth>> = SUM(CASE WHEN s.PaymentMonthYear = <<CharMonth>> THEN s.Month_Amount ELSE 0 END)'

, '<<DisplayMonth>>',DisplayMonth)

, '<<CharMonth>>' ,CharMonth)

FROM cteMonth

ORDER BY N

;

--===== Create the from clause using the input parameters to control the data returned.

SELECT @SQL = @SQL

+ REPLACE(

REPLACE('

, [Total] = SUM(s.Month_Amount)

FROM dbo.Source s

WHERE s.PaymentMonthYear BETWEEN <<StartMonth>> AND <<EndMonth>> --Ignores NULLs, as well

GROUP BY s.InvDate, s.InvNo, s.Client, s.Amount

ORDER BY s.InvDate, s.InvNo

;'

,'<<StartMonth>>',CONVERT(CHAR(6),DATEPART(yy,@pStartMonth)*100 + DATEPART(mm,@pStartMonth)))

,'<<EndMonth>>' ,CONVERT(CHAR(6),DATEPART(yy,@pEndMonth) *100 + DATEPART(mm,@pEndMonth)))

;

--===== If @pDebug is ON, display the Dynamic SQL. Otherwise, execute the dynamic SQL

IF @pDebug = 1 PRINT @sql;

ELSE EXEC (@SQL);

;

Notice that there's zero chance for SQL Injection here because the input parameters are NOT character based.

I also use REPLACE(@SQL,'<<SomeToken>>',SomeString) to make the dynamic SQL easier to read and troubleshoot instead of messing around with breaking the string with concatenation and the like.

{EDIT}

If you don't have a Tally Table that starts at "1" or maybe don't even know what it is, now's a good time to learn about it. Please see the following article for what a Tally Table is and how it can replace certain WHILE loops. Think of it as a Jedi Knight building his own Light-Sabre.

http://www.sqlservercentral.com/articles/T-SQL/62867/

I also presumed to take the next logical step of providing a total for each row.