Ravindu Gamage Ravindu Gamage - 1 month ago 5x
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'


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'