Post reply

CrossTab multiple when clauses

  • August 3, 2016 at 2:22 pm

    #320337

    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 DecJanFebMarAprMayJun

    1-NovBal0000000

    1-Dec0Bal000000

    1-Jan00 Bal00000

    1-Feb000Bal0000

    1-Mar0000Bal000

    1-Apr00000 Bal00

    1-May000000Bud0

    1-Jun000000 0 Bud

  • August 3, 2016 at 3:39 pm

    #1893641

    Try nesting CASE expressions.

    Instead of

    THEN MonthlyBalance END

    use

    THEN CASE WHEN Mth <= @EndDate THEN MonthlyBalance ELSE MonthlyBudget END END

    Remember, as an expression, CASE can be nested just like any function. Using the second example above, your CrossTab will sum up budget values instead of balance values whenever the date in MTH is greater than your end date.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • August 4, 2016 at 12:27 am

    #1893671

    Can you post the DDL, sample dara and the expected results?

    😎

  • August 4, 2016 at 6:59 am

    #1893756

    What I ended up doing is making 2 selects (select monthlybalance up to @EndDate and then another from @EndDate to @StartDate+11 for a full year) then union'd the data. Now I can use the original cross tab with this new table and fields.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply