Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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=metrics.id

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

My attempt was simply to calculate it and not use the cya variable.

It's not so much an ordering issue as a processing issue you had. The calculated columns of calquarter and calyear will not be available for re-use in the same query, so to get your first attempt to work you would have needed to use the following:

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,

((year(date_collected))*10

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 cyq

from fact_data

join metrics on fact_data.metric_id=metric

where entity_id=1 and metric_id=1

order by name

Your second attempt worked because the nested query would be processed first, therefore making the calculated fields available in the main query. I'm not sure from a processing point of view which method would be less of a load on the server but I'm pretty sure both are valid options.

Hope this makes sense,

Mark.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I believe you will need to use the following to get the value you desire.

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,

((year(date_collected))*10

from fact_data

join metrics on fact_data.metric_id=metric

where entity_id=1 and metric_id=1

order by name