August 3, 2016 at 2:21 pm
My sp has @StartDate (month and day will always be 11 and 01 ie November 1 and the year just changes) and an @EndDate. I require a column for each month, then either a MonthlyBalance or MonthlyBudget amount.
I have it working for the Monthly Balance amounts. Say a user enters @StartDate='2015-1-01' and @EndDate='2016-04-01'. With my current code, there would be zeros in all the months past the @EndDate. Instead of zeros, I want it to pull the MontlyBudget amount field.
This code works up to the @EndDate:
SUM( CASE WHEN Convert(date,Mth) >= DATEADD(month, 0, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 1, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END) as 'Nov'
, SUM( CASE WHEN Convert(date,Mth) >= DATEADD(month, 1, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 2, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END) as 'Dec'
, SUM( CASE WHEN Convert(date,Mth) >= DATEADD(month, 2, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 3, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END) as 'Jan'
, SUM( CASE WHEN Convert(date,Mth) >= DATEADD(month, 3, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 4, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END) as 'Feb'
, SUM( CASE WHEN Convert(date,Mth) >= DATEADD(month, 4, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 5, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END) as 'Mar'
, SUM( CASE WHEN Convert(date,Mth) >= DATEADD(month, 5, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 6, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END) as 'Apr'
, SUM( CASE WHEN Convert(date,Mth) >= DATEADD(month, 6, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 7, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END) as 'May'
, 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'
, SUM( CASE WHEN Convert(date,Mth) >= DATEADD(month, 8, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 9, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END) as 'Jul'
, SUM( CASE WHEN Convert(date,Mth) >= DATEADD(month, 9, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 10, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END) as 'Aug'
, SUM( CASE WHEN Convert(date,Mth) >= DATEADD(month, 10, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 11, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END) as 'Sep'
, SUM( CASE WHEN Convert(date,Mth) >= DATEADD(month, 11, CONVERT( DATE, @StartDate)) AND Convert(date,Mth) < DATEADD(month, 12, CONVERT( DATE, @StartDate)) THEN MonthlyBalance END) as 'Oct'
FROM #SG_MonthlyTotals
Group by....
If you look at November, it would give the Monthly Balance for Mth=11-01 and zeros for all the other Mth months then sums into one value. Works great.
I tried adding when Convert(date,Mth)>@EndDate then MonthlyBudget but then each When statement becomes true.
I just cannot figure out how to get a MonthlyBalance for months up to and including the EndDate then MonthlyBudget for months after EndDate.
This should be the result set with @EndDate of April
MthNovDecJanFebMarAprMayJun
1-NovBal0000000
1-Dec0Bal000000
1-Jan00Bal00000
1-Feb000Bal0000
1-Mar0000Bal000
1-Apr00000Bal00
1-May000000Bud0
1-Jun0000000Bud
August 3, 2016 at 4:19 pm
Flagging this as a duplicate post. Go HERE for discussion.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply