Natchiket
asked on
Calculated field based on another calculated field in SQL server query
In the following query I have two calculated columns SlotTime and SlotBreak and I wish to subtract them to create SlotWorked but SQL Server claims SlotTime and SlotBreak are invalid column names
select R.site_id,L.Activity,L.PT, L.Rate,'1/ ' + DateName(month,R.week_comm encing) + '/' + Cast(Year(R.week_commencin g) as varchar) as FDM,
L.D1AB AS DayAbs,
L.D1F-L.D1S as SlotTime,
Case when L.D1F-L.D1S < 6.02/24 then 0 else 0.5/24 end as SlotBreak,
([SlotTime]-[SlotBreak]) as SlotWorked
from
tblRotas R inner join tblRotaLines L on R.Rota_ID=L.rota_id
select R.site_id,L.Activity,L.PT,
L.D1AB AS DayAbs,
L.D1F-L.D1S as SlotTime,
Case when L.D1F-L.D1S < 6.02/24 then 0 else 0.5/24 end as SlotBreak,
([SlotTime]-[SlotBreak]) as SlotWorked
from
tblRotas R inner join tblRotaLines L on R.Rota_ID=L.rota_id
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your column names have to exist before you can refer to them....
So, SlotTime and SlotBreak only come into existence after the select query.
Which means, either calculate after the select if expressed as a subquery, or, as part of a CTE query.
Raja has shown the method for the subquery above....
So, here is an option by using a CTE query - pretty much the same as a subquery, except you nominate the (sub)query first by giving it a name, and then you can use that named query in subsequent usage....
And, just to fix a small error above in the long hand version by Raja....
Hope that helps - to read more about the CTE (short for Common Table Expression) see https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15
Oh, and an easier way to format a date as a string is to use the FORMAT command.
For example : select format(getdate(),'01/MMM/y yy') will return 01/Oct/2019
So, SlotTime and SlotBreak only come into existence after the select query.
Which means, either calculate after the select if expressed as a subquery, or, as part of a CTE query.
Raja has shown the method for the subquery above....
So, here is an option by using a CTE query - pretty much the same as a subquery, except you nominate the (sub)query first by giving it a name, and then you can use that named query in subsequent usage....
;with MyCTE as
( select R.site_id,L.Activity,L.PT,L.Rate,'1/' + DateName(month,R.week_commencing) + '/' + Cast(Year(R.week_commencing) as varchar) as FDM,
L.D1AB AS DayAbs,
L.D1F-L.D1S as SlotTime,
Case when L.D1F-L.D1S < 6.02/24 then 0 else 0.5/24 end as SlotBreak
from tblRotas R
inner join tblRotaLines L on R.Rota_ID=L.rota_id
) select *, (SlotTime - SlotBreak) AS SlotWorked
from MyCTE
And, just to fix a small error above in the long hand version by Raja....
select R.site_id,L.Activity,L.PT,L.Rate,'1/' + DateName(month,R.week_commencing) + '/' + Cast(Year(R.week_commencing) as varchar) as FDM,
L.D1AB AS DayAbs,
L.D1F-L.D1S as SlotTime,
Case when L.D1F-L.D1S < 6.02/24 then 0 else 0.5/24 end as SlotBreak,
(L.D1F-L.D1S) - (Case when L.D1F-L.D1S < 6.02/24 then 0 else 0.5/24 end) as SlotWorked
from tblRotas R
inner join tblRotaLines L on R.Rota_ID=L.rota_id
Hope that helps - to read more about the CTE (short for Common Table Expression) see https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15
Oh, and an easier way to format a date as a string is to use the FORMAT command.
For example : select format(getdate(),'01/MMM/y
ASKER