Ravindu Gamage Ravindu Gamage - 1 year ago 64
SQL Question

How to perform arithmetic on a column defined on a SELECT statement

SELECT DATEDIFF(MINUTE, j.start_time , j.end_time) as job_duration,
(jtype.cost_per_minute * job_duration) as job_cost

All the joins and from statements are done it runs without error when there is no arithmetic to be done but with the multiplication it gives an error. Not sure what to do. Any help would be appreciated thanks in advance.

Invalid column name 'job_duration'

Answer Source

job_duration does not exist until the query gets executed you can do it by nesting into another sql clause or by doing this:

 SELECT DATEDIFF(MINUTE, j.start_time , j.end_time) AS job_duration, (jtype.cost_per_minute * DATEDIFF(MINUTE, j.start_time , j.end_time)) AS 'job_cost'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download