How to use a calculated column to calculate another column in the same query using a subquery

Question

I'm trying to add a new calculated field (TravelTime) based on the NLength and NSpeedLimit fields from the following query

SELECT          
        sc.OBJECTID, 
        sn.Name, 
        case    when hn.side = 'Right Side' then ''
            else sc.LCity
        end as LCity, 
        case    when hn.side = 'Left Side' then ''
            else sc.RCity
        end as RCity, 
        case    when hn.side = 'Right Side' then ''
            else sc.LZip
        end as LZip, 
        case    when hn.side = 'Left Side' then ''
            else sc.RZip
        end as RZip, 
        sc.SHAPE.STLength() AS NLength,
        ISNULL(sc.SpeedLimit,1) AS NSpeedLimit

FROM            STREETNAME AS sn 
    INNER JOIN
                STREETHASSTREETNAME AS hn ON 
                         sn.GlobalID = hn.GlobalID AND 
                         hn.Role = 'Primary'
INNER JOIN      STREETCENTERLINE AS sc ON 
                hn.GlobalID = sc.GlobalID

The new calculated field is TravelTime = NLength/(NSpeedLimit*88) but I can't add NLength/(NSpeedLimit*88) AS TravelTimein the select statement. I know I need to do a subquery but I don't know where it's supposed to go.

Does this answer your question? [How to use one data field into another case expression in the same query](https://stackoverflow.com/questions/67363054/how-to-use-one-data-field-into-another-case-expression-in-the-same-query) — Dale K, May 19 '21 at 21:15
Or even [this](https://stackoverflow.com/questions/67274101/sql-use-value-from-different-column-on-same-select/67274163#67274163) — Dale K, May 19 '21 at 21:22
Does this answer your question? [Possible to store value of one select column and use it for the next one?](https://stackoverflow.com/questions/65818438/possible-to-store-value-of-one-select-column-and-use-it-for-the-next-one) — Charlieface, May 19 '21 at 22:46

score 1 · Accepted Answer · answered May 19 '21 at 23:09

You can use Temp_tables, Derived tables or Common table expressions (CTE) to obtain the result. Simple approach would be Derived table as you dont need much more coding.

  SELECT A.*
  ,  A.NLength/(A.NSpeedLimit * 88)  as [TravelTime]
  FROM
  (


  SELECT          
    sc.OBJECTID, 
    sn.Name, 
    case    when hn.side = 'Right Side' then ''
        else sc.LCity
    end as LCity, 
    case    when hn.side = 'Left Side' then ''
        else sc.RCity
    end as RCity, 
    case    when hn.side = 'Right Side' then ''
        else sc.LZip
    end as LZip, 
    case    when hn.side = 'Left Side' then ''
        else sc.RZip
    end as RZip, 
    sc.SHAPE.STLength() AS NLength,
    ISNULL(sc.SpeedLimit,1) AS NSpeedLimit

  FROM    STREETNAME AS sn 
   INNER JOIN
            STREETHASSTREETNAME AS hn ON 
                     sn.GlobalID = hn.GlobalID AND 
                     hn.Role = 'Primary'
          INNER JOIN      STREETCENTERLINE AS sc ON 
              hn.GlobalID = sc.GlobalID

            ) AS A

score 0 · Answer 2 · edited May 19 '21 at 22:45

You can add the columns in the FROM clause using apply:

SELECT . . .
       v.NLength, v.NSpeedLimit, 
        (v.NLength / (v.NSpeedLimit*88)) as TravelTime
FROM STREETNAME sn JOIN
     STREETHASSTREETNAME hn
     ON sn.GlobalID = hn.GlobalID AND 
        hn.Role = 'Primary' JOIN
     STREETCENTERLINE sc
     ON hn.GlobalID = sc.GlobalID CROSS APPLY
     (VALUES (sc.SHAPE.STLength(), COALESCE(sc.SpeedLimit, 1)
     ) v(NLength, NSpeedLimit)

How to use a calculated column to calculate another column in the same query using a subquery

2 Answers2

Related