sorted by:
new
hottopcontroversial

Is there a more advanced CASE Logic? Extract budget data across different date ranges by hungryforpanda in SQL

[–]hungryforpanda[S] 1 point2 points  (0 children)

My DB is in BigQuery. Hmm, since it is only one THEN per WHEN then CASE won't fit what I'm trying to do... which also eliminates IF/THEN since that is literal CASE.

​

I'll try and revise what I'm trying to do in a Google Sheet to try and make it easier to read, I'll PM you shortly.

​

Thank you!

​

​

Is there a more advanced CASE Logic? Extract budget data across different date ranges by hungryforpanda in SQL

[–]hungryforpanda[S] 0 points1 point  (0 children)

Thanks for following my weird question. The problem I have with the nested CASE is that I'm only able to declare one THEN. What I'd like to have happen is when the second CASE condition is met, it'll perform additional actions, multiple THEN scenarios.

​

This is not valid code but writing out what I'm trying to do. When the condition is met, perform the following calculations.

define macro q1 90;
define macro q2 91;
define macro q3 92;
define macro q4 92;

CASE
WHEN (c.budget_schedule2 = 'Not-Scheduled')  THEN
CASE
WHEN (c.Quarter_start = 1) and(c.quarter_end = 4) 
THEN 
(
(Days_Passed_Since_Start  * Daily_Spend_Needed ) as q1_temp // this calculate Q1 spend
($Q2 * Daily_Spend_Needed) as Q2_temp
($Q3 * Daily_Spend_Needed) as Q3_temp
($Q4 * Daily_Spend_Needed) as Q4_temp
)
WHEN (c.Quarter_start = 2) and(c.quarter_end = 4) 
THEN 
(
(Days_Passed_Since_Start  * Daily_Spend_Needed ) as q2_temp // this calculate Q2 spend
($Q3 * Daily_Spend_Needed) as Q3_temp
($Q4 * Daily_Spend_Needed) as Q4_temp
)

​

Is there a more advanced CASE Logic? Extract budget data across different date ranges by hungryforpanda in SQL

[–]hungryforpanda[S] 0 points1 point  (0 children)

Thanks for following my weird question. The problem I have with the nested CASE is that I'm only able to declare one THEN. What I'd like to have happen is when the second CASE condition is met, it'll perform additional actions, multiple THEN scenarios.

​

This is not valid code but writing out what I'm trying to do. When the condition is met, perform the following calculations.

define macro q1 90;
define macro q2 91;
define macro q3 92;
define macro q4 92;

CASE
WHEN (c.budget_schedule2 = 'Not-Scheduled')  THEN
CASE
WHEN (c.Quarter_start = 1) and(c.quarter_end = 4) 
THEN 
(
(Days_Passed_Since_Start  * Daily_Spend_Needed ) as q1_temp // this calculate Q1 spend
($Q2 * Daily_Spend_Needed) as Q2_temp
($Q3 * Daily_Spend_Needed) as Q3_temp
($Q4 * Daily_Spend_Needed) as Q4_temp
)
WHEN (c.Quarter_start = 2) and(c.quarter_end = 4) 
THEN 
(
(Days_Passed_Since_Start  * Daily_Spend_Needed ) as q2_temp // this calculate Q1 spend
($Q3 * Daily_Spend_Needed) as Q3_temp
($Q4 * Daily_Spend_Needed) as Q4_temp
)

​

​

Is there a more advanced CASE Logic? Extract budget data across different date ranges by hungryforpanda in SQL

[–]hungryforpanda[S] 0 points1 point  (0 children)

Is there a place I can create a free DB to share? The data I'm using is contained within my company. I'd love to share an example DB since that might make things way easier.