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