Link to home
Start Free TrialLog in
Avatar of Natchiket
NatchiketFlag for United Kingdom of Great Britain and Northern Ireland

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_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,
([SlotTime]-[SlotBreak]) as SlotWorked
from
tblRotas R inner join tblRotaLines L on R.Rota_ID=L.rota_id
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Natchiket

ASKER

Thanks Raja
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....

;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  

Open in new window



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 

Open in new window


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/yyy')  will return 01/Oct/2019