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)=LatestYear Num or
MONTH(TodayDate)=12 and YEAR(TodayDate)=LatestYear Num -1 or
MONTH(TodayDate)=11 and YEAR(TodayDate)=LatestYear Num -1
THEN ConvertedQuantity/3
ELSE CASE WHEN LatestMonthNum=1 THEN
CASE WHEN MONTH(TodayDate)=12 and YEAR(TodayDate)=LatestYear Num-1 or
MONTH(TodayDate)=11 and YEAR(TodayDate)=LatestYear Num -1 or
MONTH(TodayDate)=10 and YEAR(TodayDate)=LatestYear Num -1
THEN ConvertedQuantity/3
ELSE CASE WHEN MONTH(TodayDate)= LatestMonthNum-1 and YEAR(TodayDate)=LatestYear Num or MONTH(TodayDate)= LatestMonthNum-2 and YEAR(TodayDate)=LatestYear Num or MONTH(TodayDate)= LatestMonthNum-3 and YEAR(TodayDate)=LatestYear Num
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)=LatestYear Num) or
(MONTH(TodayDate)=12 and YEAR(TodayDate)=LatestYear Num -1) or
(MONTH(TodayDate)=11 and YEAR(TodayDate)=LatestYear Num -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