Hi, I am trying to write a report for our GL account budgets, broken down by monthly amounts. We soft close 52 periods on a 4-4-5 schedule. So my budgets are loaded by periods, and I’m trying to calculate the month from the period. Here is the nested case expression i’m using, which should work great, but I get the error: “Case expressions may only be nested to level 10.”
Anyone have an idea of another way I could conclude the month from the period?
(
case when (
1 <= GLBudgetDtl.FiscalPeriod
and GLBudgetDtl.FiscalPeriod <= 4
) then 1 else (
case when (
5 <= GLBudgetDtl.FiscalPeriod
and GLBudgetDtl.FiscalPeriod <= 8
) then 2 else (
case when (
9 <= GLBudgetDtl.FiscalPeriod
and GLBudgetDtl.FiscalPeriod <= 13
) then 3 else (
case when (
14 <= GLBudgetDtl.FiscalPeriod
and GLBudgetDtl.FiscalPeriod <= 17
) then 4 else (
case when (
18 <= GLBudgetDtl.FiscalPeriod
and GLBudgetDtl.FiscalPeriod <= 21
) then 5 else (
case when (
22 <= GLBudgetDtl.FiscalPeriod
and GLBudgetDtl.FiscalPeriod <= 26
) then 6 else (
case when (
27 <= GLBudgetDtl.FiscalPeriod
and GLBudgetDtl.FiscalPeriod <= 30
) then 7 else (
case when (
31 <= GLBudgetDtl.FiscalPeriod
and GLBudgetDtl.FiscalPeriod <= 34
) then 8 else (
case when (
35 <= GLBudgetDtl.FiscalPeriod
and GLBudgetDtl.FiscalPeriod <= 39
) then 9 else (
case when (
40 <= GLBudgetDtl.FiscalPeriod
and GLBudgetDtl.FiscalPeriod <= 43
) then 10 else (
case when (
44 <= GLBudgetDtl.FiscalPeriod
and GLBudgetDtl.FiscalPeriod <= 47
) then 11 else 12 end
) end
) end
) end
) end
) end
) end
) end
) end
) end
) end
)
(case
when (1 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 4) then 1
when (5 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 8) then 2
end)
That is exactly what I needed to see! Did not realize I could write it like that. Thanks so much!!
case
when (1 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 4) then 1
when (5 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 8) then 2
when (9 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 13) then 3
when (14 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 17) then 4
when (18 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 21) then 5
when (22 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 26) then 6
when (27 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 30) then 7
when (31 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 34) then 8
when (35 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 39) then 9
when (40 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 43) then 10
when (44 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 47) then 11
else 12
end
Hi Calvin, good point on the AND. That would work on the first condition, but wouldn’t it fail if I did that on every condition in the statement? If it was period 1, I would want to return month 1, but this would not work, because both conditions are met:
when (GLBudgetDtl.FiscalPeriod <= 4) then 1
when (GLBudgetDtl.FiscalPeriod <= 8) then 2
once it satisfies one, it’s done. It doesn’t keep going through the loop. So make sure you pay attention to the order when you make something like this.
I think you wanted >= on the first first half of each ANDed condition…
Calvin - I usually put the variable first too I was seeing if it would accept 1 < x < 4, but it did not like that, and inserting an AND statement was the quickest edit haha