I have payments per cnsmr_accnt_id going back several months. I wish to see the total payments per month for the last 4 months. Something like this:
Cnsmr_accnt_id | Wrkgrp_nm | Apr15_Tot | May15_Tot | Jun15_Tot | Jul15_Tot 12345 |Workgrp1 | 123424 | 1243255 | 232342 | 23232323 12347 |Workgrp4 | 123323 | 1244455 | 324342 | 232323
I am trying to use a Pivot to do this but keep getting an error when using the following code. I suspect it's something to do with the variable @Dates scope.
@Dates is giving me the value Apr15,May15,Jun15,Jul15 which is right.
If I substitute this directly into the Select in the place of @Dates, the query works. But the moment I use @Dates, it gives an error. (Incorrect syntax near '@Dates'.)
I have also tried putting the whole "formula" used to Set @Dates into the select statement but this also gives an error. (Incorrect syntax near the keyword 'left')
Declare @Dates as nvarchar(max);
--Builds up a string of dates for the last 4 months.
Set @Dates = left(datename(Month, datefromparts(year(dateadd(month, -3, getdate())), month(getdate())-3, 1)), 3) +
right(datename(year, datefromparts(year(dateadd(month, -3, getdate())), month(getdate())-3, 1)), 2) + ',' +
left(datename(Month, datefromparts(year(dateadd(month, -2, getdate())), month(getdate())-2, 1)), 3) +
right(datename(year, datefromparts(year(dateadd(month, -2, getdate())), month(getdate())-2, 1)), 2)+ ',' +
left(datename(Month, datefromparts(year(dateadd(month, -1, getdate())), month(getdate())-1, 1)), 3) +
right(datename(year, datefromparts(year(dateadd(month, -1, getdate())), month(getdate())-1, 1)), 2)+ ',' +
left(datename(Month, getdate()), 3) +
right(datename(year, getdate()), 2);
--Print @dates
--Example output = Apr15,May15,Jun15,Jul15
--Use a pivot to get the totals per cnsmr_accnt_id for the last 4 months.
Select *
from
(
Select
capj.cnsmr_accnt_id,
wrkgrp_nm,
max(UDEFINST_AMT) as Instlmnt,
sum(cnsmr_accnt_pymnt_amnt) as Mnth_Tot,
--Gives the Month & year (Eg Jul15)
left(datename(Month, cnsmr_accnt_pymnt_pstd_dt), 3) + right(datename(year, cnsmr_accnt_pymnt_pstd_dt), 2) as Month_Yr
from
dbo.cnsmr_accnt_pymnt_jrnl capj
inner join
UDEFGENERAL UG
on
capj.cnsmr_accnt_id = UG.cnsmr_accnt_id
Inner join
wrkgrp w
on
capj.wrkgrp_id = w.wrkgrp_id
where
cnsmr_accnt_pymnt_stts_cd in (5)
and cnsmr_accnt_pymnt_pstd_dt between
--Go back to the 1st day 4 months back
datefromparts(year(dateadd(month, -3, getdate())), month(getdate())-3, 1)
and
getdate()
group by capj.cnsmr_accnt_id,
left(datename(Month, cnsmr_accnt_pymnt_pstd_dt), 3) + right(datename(year, cnsmr_accnt_pymnt_pstd_dt), 2),
wrkgrp_nm) as Mnth_Tot_Tbl
pivot
(
sum(Mnth_Tot)
for
Month_Yr in (@Dates)) as Piv
If you want the last 4 months worth of data I would suggest you approach this differently, rather than grouping by month and year, group by the number of months since this month. So rather than trying to dynamically pivot, you are just pivoting on static numbers (0, 1, 2, 3);
SELECT *
FROM ( SELECT capj.cnsmr_accnt_id,
wrkgrp_nm,
Instlmnt = MAX(udefinst_amt),
Mnth_Tot = SUM(cnsmr_accnt_pymnt_amnt),
MonthsSinceToday = DATEDIFF(MONTH, cnsmr_accnt_pymnt_pstd_dt, GETDATE())
FROM dbo.cnsmr_accnt_pymnt_jrnl AS capj
INNER JOIN Udefgeneral AS ug
ON capj.cnsmr_accnt_id = ug.cnsmr_accnt_id
INNER JOIN wrkgrp AS w
ON capj.wrkgrp_id = w.wrkgrp_id
WHERE cnsmr_accnt_pymnt_stts_cd IN (5)
AND cnsmr_accnt_pymnt_pstd_dt >= DATEADD(MONTH, DATEDIFF(MONTH, '19000401', GETDATE()), '19000101')
AND cnsmr_accnt_pymnt_pstd_dt < GETDATE()
GROUP BY cnsmr_accnt_id, wrkgrp_nm, DATEDIFF(MONTH, cnsmr_accnt_pymnt_pstd_dt, GETDATE())
) AS d
PIVOT
( SUM(Mnth_Tot)
FOR MonthsSinceToday IN ([0], [1], [2], [3])
) AS pvt;
The downside of this approach is that your dates won't be column headers, but the upside is you don't need to use dynamic sql, and column names should be fairly easily handled by your presentation layer anyway.