opike
asked on
Trying to get a calculation to work in mysql query
I'm trying to get the cyq calculation to work in the following query but it keeps returning null:
select name,value,date_collected,
case month(date_collected) when 1 then 1 when 2 then 1 when 3 then 1
when 4 then 2 when 5 then 2 when 6 then 2
when 7 then 3 when 8 then 3 when 9 then 3
when 10 then 4 when 11 then 4 when 12 then 4
end as calquarter,
year(date_collected) as calyear,
(calyear*10+calquarter) as cyq
from fact_data
join metrics on fact_data.metric_id=metric
where entity_id=1 and metric_id=1
order by name
select name,value,date_collected,
case month(date_collected) when 1 then 1 when 2 then 1 when 3 then 1
when 4 then 2 when 5 then 2 when 6 then 2
when 7 then 3 when 8 then 3 when 9 then 3
when 10 then 4 when 11 then 4 when 12 then 4
end as calquarter,
year(date_collected) as calyear,
(calyear*10+calquarter) as cyq
from fact_data
join metrics on fact_data.metric_id=metric
where entity_id=1 and metric_id=1
order by name
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You are correct in saying those values were not yet available.
My attempt was simply to calculate it and not use the cya variable.
My attempt was simply to calculate it and not use the cya variable.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yep, you are right OnAlLearningCurve, I didn't notice that calcQuarter was also being derived.
Thanks for the points :)
Glad I could help.
Mark.
Glad I could help.
Mark.
ASKER
But I did get it to work with nesting the queries like this:
select name,(calyear*10+calquarte
(select name,value,date_collected,
case month(date_collected) when 1 then 1 when 2 then 1 when 3 then 1
when 4 then 2 when 5 then 2 when 6 then 2
when 7 then 3 when 8 then 3 when 9 then 3
when 10 then 4 when 11 then 4 when 12 then 4
end as calquarter,
year(date_collected) as calyear
from fact_data
join metrics on fact_data.metric_id=metric
where entity_id=1 and metric_id=1) as t1
order by name,date_collected
I guess it's some kind of ordering issue where the other calquarter and calyear values aren't available for another calculation within the same select statement.