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
Mth Nov Dec Jan Feb Mar Apr May Jun
1-Nov Bal 0 0 0 0 0 0 0
1-Dec 0 Bal 0 0 0 0 0 0
1-Jan 0 0 Bal 0 0 0 0 0
1-Feb 0 0 0 Bal 0 0 0 0
1-Mar 0 0 0 0 Bal 0 0 0
1-Apr 0 0 0 0 0 Bal 0 0
1-May 0 0 0 0 0 0 Bud 0
1-Jun 0 0 0 0 0 0 0 Bud
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