I've got a beastmode I am trying to create that will calculate the days remaining in a quarter.
- If the quarter is already over I want to show a value of 0 (not a negative number).
- If the quarter hasn't started I want to show 91 (that's the number of days in a quarter)
Any help would be appreciated. Also if this is better handled in ETL I can go that way as well.
This is the concept I am working with (not currently working, and does not include the):
Case
when
DATEDIFF(CURDATE(),
case
when `Count Quarter`='Q1' then DATE(3/31/2020)
when `Count Quarter`='Q2' then DATE(6/30/2020)
when `Count Quarter`='Q3' then DATE(9/30/2020)
when `Count Quarter`='Q4' then DATE(12/31/2020)
else 0
end
)>91 then 91
when
DATEDIFF(CURDATE(),
case
when `Count Quarter`='Q1' then DATE(3/31/2020)
when `Count Quarter`='Q2' then DATE(6/30/2020)
when `Count Quarter`='Q3' then DATE(9/30/2020)
when `Count Quarter`='Q4' then DATE(12/31/2020)
else 0
end
)<0 then 0
else
DATEDIFF(CURDATE(),
case
when `Count Quarter`='Q1' then DATE(3/31/2020)
when `Count Quarter`='Q2' then DATE(6/30/2020)
when `Count Quarter`='Q3' then DATE(9/30/2020)
when `Count Quarter`='Q4' then DATE(12/31/2020)
else 0
end
)
end