nirajkrishna

asked on

# Nested Case Statement Syntax

I am trying to write a nested case statement to look back at the 3 prior months and give me an average. But I keep getting an error. Where am I wrong?

CASE

WHEN LatestMonthNum=2

THEN

CASE

WHEN MONTH(TodayDate)=1 and YEAR(TodayDate)=LatestYearNum or

MONTH(TodayDate)=12 and YEAR(TodayDate)=LatestYearNum -1 or

MONTH(TodayDate)=11 and YEAR(TodayDate)=LatestYearNum -1

THEN ConvertedQuantity/3

ELSE CASE WHEN LatestMonthNum=1 THEN

CASE WHEN MONTH(TodayDate)=12 and YEAR(TodayDate)=LatestYearNum-1 or

MONTH(TodayDate)=11 and YEAR(TodayDate)=LatestYearNum -1 or

MONTH(TodayDate)=10 and YEAR(TodayDate)=LatestYearNum -1

THEN ConvertedQuantity/3

ELSE CASE WHEN MONTH(TodayDate)= LatestMonthNum-1 and YEAR(TodayDate)=LatestYearNum or MONTH(TodayDate)= LatestMonthNum-2 and YEAR(TodayDate)=LatestYearNum or MONTH(TodayDate)= LatestMonthNum-3 and YEAR(TodayDate)=LatestYearNum

then ConvertedQuantity/3

else null

END as 'ThreeMoAvg'

CASE

WHEN LatestMonthNum=2

THEN

CASE

WHEN MONTH(TodayDate)=1 and YEAR(TodayDate)=LatestYear

MONTH(TodayDate)=12 and YEAR(TodayDate)=LatestYear

MONTH(TodayDate)=11 and YEAR(TodayDate)=LatestYear

THEN ConvertedQuantity/3

ELSE CASE WHEN LatestMonthNum=1 THEN

CASE WHEN MONTH(TodayDate)=12 and YEAR(TodayDate)=LatestYear

MONTH(TodayDate)=11 and YEAR(TodayDate)=LatestYear

MONTH(TodayDate)=10 and YEAR(TodayDate)=LatestYear

THEN ConvertedQuantity/3

ELSE CASE WHEN MONTH(TodayDate)= LatestMonthNum-1 and YEAR(TodayDate)=LatestYear

then ConvertedQuantity/3

else null

END as 'ThreeMoAvg'

<knee-jerk reaction>

I see AND and OR in the same clauses, but no parentheses ( ) to determine order of execution.

I'm guessing the first block should be this:

CASE WHEN

(MONTH(TodayDate)=1 and YEAR(TodayDate)=LatestYearNum) or

(MONTH(TodayDate)=12 and YEAR(TodayDate)=LatestYearNum -1) or

(MONTH(TodayDate)=11 and YEAR(TodayDate)=LatestYearNum -1)

THEN ConvertedQuantity/3

Without the parentheses ( ), it's going to do each and .. or ... and as an entire set, and since Month(TodayDate) is likely a single value per row, it'll never evaluate as True and return the THEN expression, only the ELSE expression.

I see AND and OR in the same clauses, but no parentheses ( ) to determine order of execution.

I'm guessing the first block should be this:

CASE WHEN

(MONTH(TodayDate)=1 and YEAR(TodayDate)=LatestYear

(MONTH(TodayDate)=12 and YEAR(TodayDate)=LatestYear

(MONTH(TodayDate)=11 and YEAR(TodayDate)=LatestYear

THEN ConvertedQuantity/3

Without the parentheses ( ), it's going to do each and .. or ... and as an entire set, and since Month(TodayDate) is likely a single value per row, it'll never evaluate as True and return the THEN expression, only the ELSE expression.

ASKER

This is just the calculated column and not the whole query. I am cross joined to a single row table that gives me the most recent date and latest month number (LatestMonthNum) and latest year (LatestYearNum)

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

I am trying to say if the LatestMonthNum = 2 or 1 which means Jan or Feb then look at the latest month number and use the appropriate 3 prior months totals divided by 3 to give me an average. If it is not Jan or Feb then look at the data and when the month falls into the 3 prior months of today then add it to this column and divide it by 3 otherwise leave blank.

I am making a quick daily report that shows the current months performance (convertedQuantity) vs last month vs 3 prior month average.

I am making a quick daily report that shows the current months performance (convertedQuantity) vs last month vs 3 prior month average.

as kdo said, every case should finish with an end. Try to change your expression by the expression added by kdo

All that this complicated CASE statement does is divide ConvertedQuantity by 3. Or not.

You probably want record selection (the WHERE filter) to determine which rows to process and just divide by 3.

You probably want record selection (the WHERE filter) to determine which rows to process and just divide by 3.

ASKER

This worked perfectly except for at the end I had to add one more "END" before the "END AS". Thanks!

Indenting the code makes it really easy to see. :)

Open in new window

It's really tough to see what you're trying to achieve. Can you explain it? The SQL should be easy. :)

Kent