Ravindu Gamage Ravindu Gamage - 2 months ago 7
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

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'