Everything worked great for this cross tab. I now have to modify it slightly, but it's now giving me multiple values per month because of my additional syntax.
Example @StartDate='2015-11-01',@EndDate='2016-05-01'
What I'd like in each month is the MonthlyBalance for any dates <=@EndDate. If it's greater than the @EndDate, I want to use another value MonthlyBudget
Code before:
SUM( CASE WHEN Convert(date,Mth) >= DATEADD(month, 7, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 8, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END) as 'Jun'
Code after:
SUM( CASE
WHEN Convert(date,Mth) >= DATEADD(month,7, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 8, CONVERT( DATE, @StartDate))
AND Convert(date,Mth) < DATEADD(month,8, CONVERT( DATE, @EndDate))
THEN MonthlyBalance ELSE MonthlyBudget END) as 'Jun'
What's happening is that it's putting a value for all 6 rows (6 additional months).
I've tried this but it doesn't work either.
sum( CASE
WHEN Convert(date,Mth) >= DATEADD(month, 7, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 8, CONVERT( DATE, @StartDate))
THEN MonthlyBalance
when Convert(date,Mth)<Convert(date,@EndDate) then MonthlyBudget
END)
The temp table is as such:
Create Table #SG_MonthlyTotals(
GLAcct varchar(12),
GLDescription varchar(50),
AcctType varchar(5),
MainAccount varchar(5),
Location varchar(2),
Division varchar(3),
ContractType varchar(10),
Category varchar(50),
SubCategory varchar(50),
Mth date,
MonthlyBalance money,
MonthlyBudget money
)
insert into #SG_MonthlyTotals
select a.*,m.Mth,m.MonthlyBalance,b.MonthlyBudget
from #SG_GLAccounts a left outer join #SG_MonthlyBalances m
on a.GLAcct=m.GLAcct left outer join #SG_MonthlyBudgets b
on m.GLAcct=b.GLAcct and m.Mth=b.Mth