The attached code works...but I need to make the pivot part dynamic because the months will change in my Stored Procedure based on paramaters passed in for BegDate and EndDate.
This is the DYNAMIC I've been trying to get working...no go so far
It returns this error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
on this sql
declare @sql varchar(4000)
declare @columns varchar(8000)
set @columns = (select Mth from #temp group by Mth)
set @sql = 'SELECT * FROM
(SELECT [rep], [GrossRev], [Mth] as Mth from #temp) src
(Sum ([GrossRev]) FOR Mth IN (' + @columns + ') ) AS pvt'
Sum(v.[Gross Rev]) as GrossRev,
DATENAME(month, v.Funded) as Mth
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())-3)
Group by rep, v.Funded
ORDER BY v.rep, DATENAME(month, v.Funded)
SELECT Rep,Mth,Grossrev FROM #temp)P
(SUM(Grossrev) FOR Mth IN (June,July))As Pvt
order by rep