CODEWITHSUNDEEP
sql-server-2008t-sql
Chris
Chris

Reputation: 343

Trying to use a calculated column to calculate another column

I am trying to calculate a BMI field based on height in inches and weight in pounds. I wrote a sub query but something is wrong...

SELECT CASE_NUM,HEIGHT_F AS HEIGHT_ft,HEIGHT_I AS HEIGHT_inches,WEIGHT_P AS WEIGHT_lbs,WEIGHT_O AS WEIGHT_oz,
PRESS_SYST,PRESS_DIAST,HEART_RATE,RESP_RATE,WAIST_CIR,APPROVED_DATE,STAFF_ID,
S.SORT_NAME AS SERVER_NAME,CAST(PRESS_SYST AS VARCHAR(5)) + '/' + CAST (PRESS_DIAST AS VARCHAR(5)) AS BP,

HEIGHT_F * 12 + HEIGHT_I 
AS HEIGHT_TOTAL_IN

FROM (
 SELECT 
  (HEIGHT_TOTAL_IN * HEIGHT_TOTAL_IN / WEIGHT_P) * 703 AS BMI

FROM AZCLCDEV A
INNER JOIN CDCLIENT C
ON A.CLIENT_ID = C.ID
INNER JOIN CAEMP S
ON A.STAFF_ID = S.ID

Upvotes: 0

Views: 131

Answers (1)

tgolisch
tgolisch

Reputation: 6744

I think I can see what you were trying to do. Try this query:

SELECT *, (HEIGHT_TOTAL_IN * HEIGHT_TOTAL_IN / WEIGHT_P) * 703 AS BMI
FROM (
    SELECT CASE_NUM, HEIGHT_F AS HEIGHT_ft, HEIGHT_I AS HEIGHT_inches, 
           WEIGHT_P AS WEIGHT_lbs,WEIGHT_O AS WEIGHT_oz,
           PRESS_SYST, PRESS_DIAST, HEART_RATE, RESP_RATE, WAIST_CIR, 
           APPROVED_DATE, STAFF_ID, S.SORT_NAME AS SERVER_NAME,
           CAST(PRESS_SYST AS VARCHAR(5)) + '/' + CAST (PRESS_DIAST AS VARCHAR(5)) AS BP,
           HEIGHT_F * 12 + HEIGHT_I AS HEIGHT_TOTAL_IN
    FROM AZCLCDEV A
         INNER JOIN CDCLIENT C
             ON A.CLIENT_ID = C.ID
         INNER JOIN CAEMP S
             ON A.STAFF_ID = S.ID
    ) OrigQuery

Upvotes: 2

Related Questions