The atteached code, supplied by an EE Expert works great.
The data is output as:
rep January February etc
myrep 1000 1250 etc
Is there a way to add a totals column and if possible an average per month column?
New output desired...
rep January February etc Totals AVG
myrep 1000 1250 etc 2250 1125
Sum(v.[Gross Rev]) as GrossRev,
DATENAME(month, v.Funded) as Mth ,
month(v.Funded) as MthI
FROM CRMPROD_01.dbo.v_P_Reporting v
WHERE v.Funded is not null
and v.Status <> 'Closed'
and (YEAR(v.Funded) = YEAR(getdate()))
and (MONTH(v.Funded) > MONTH(getdate())-@)
Group by rep, v.Funded
ORDER BY v.rep, month(v.Funded)
--Create Dynamic SQl and Pivot
declare @sql varchar(4000)
declare @columns varchar(8000)
select @columns = coalesce(@columns+',','') + '[' + Mth + ']'
group by Mth, MthI
order by MthI
set @sql = '
SELECT [rep], ' + @columns + '
(SELECT [rep], [GrossRev], [Mth] as Mth from #temp) src
(Sum ([GrossRev]) FOR Mth IN (' + @columns + ') ) AS pvt'
Drop table #temp