Calculation In Select Statement

eusanpe · May 18th, 2009, 04:07 PM

Hello All,

I am looking for a way to perform a calculation on the results of weight and height. I need see if there is a result for the height and weight for a person on the same date since they are entered at the same time. If so, then I need to perform the calculation (weight/(height*height) x 703).

Any help would be appreciated.

Thank you.

Code:


USE TempDB
GO

CREATE TABLE dbo.HeightWeight
(      Name          VARCHAR(50),
        Date          DateTime,
        Description   VARCHAR(50),
        Result        VARCHAR(20)
)
INSERT INTO HeightWeight
(Name,Date,Description,Result)
SELECT 'Test1','2009-05-01','Height','60' UNION ALL
SELECT 'Test1','2009-05-01','Weight','175' UNION ALL
SELECT 'Test1','2009-05-02','Height','60' UNION ALL
SELECT 'Test1','2009-05-02','Weight','176' UNION ALL
SELECT 'Test2','2009-05-03','Height','65' UNION ALL
SELECT 'Test2','2009-05-03','Weight','190' UNION ALL
SELECT 'Test3','2009-05-04','Height','68' UNION ALL
SELECT 'Test3','2009-05-04','Weight','180' UNION ALL
SELECT 'Test4','2009-05-05','Height','63' UNION ALL
SELECT 'Test4','2009-05-05','Weight','185'

Jeff Moden · May 18th, 2009, 06:48 PM

Code:

 SELECT Date,Name,
        MAX(CASE WHEN Description='Weight' THEN Result END)
    /POWER(MAX(CASE WHEN Description='Height' THEN Result END),2)
    * 703
  FROM #HeightWeight
 GROUP BY Date,Name
 ORDER BY Date,Name

Jeff Moden · May 18th, 2009, 06:52 PM

p.s. I really hate the way this forum scrambles simple code.

Old Pedant · May 18th, 2009, 07:40 PM

Ummm...Jeff, he wanted that to ONLY happen when the height and weight were entered ON THE SAME DATE.

I don't see any place in there where he asked for a maximum of either value.

I think what he meant was this:

Code:

SELECT W.Name, W.Date, (703.0 * W.result / POWER(H.result,2) ) AS BMI
FROM HeightWeight AS W, HeightWeight AS H
WHERE H.Date = W.Date
ORDER BY W.Name, W.Date

Except that for some silly reason he made the RESULT field a VARCHAR(20) datatype. HORRIBLE!

So he needs a CAST or CONVERT in there:

Code:

SELECT W.Name, 
       W.Date, 
       (703.0 * CONVERT(FLOAT,W.result) / POWER(CONVERT(FLOAT,H.result),2) ) AS BMI
FROM HeightWeight AS W, HeightWeight AS H
WHERE H.Date = W.Date
ORDER BY W.Name, W.Date

Ugh.

eusanpe : WHY did you make the RESULT field anything other than FLOAT???

p.s.: Since he noted the use of dbo.HeightWeight, this must be SQL Server, so the syntax for both CONVERT and POWER is correct.

eusanpe · May 18th, 2009, 07:52 PM

Thank you both for your help.

It is not a Float field because there are other Descriptions that have values that are text, like yes, no, true. I didn't design the thing.

Thanks again

Old Pedant · May 18th, 2009, 08:19 PM

Quote:

I didn't design the thing.

Oh, the joys of working on legacy applications. I undestand all too well.

Best of luck!

Jeff Moden · May 19th, 2009, 05:20 PM

Quote:

Originally Posted by Old Pedant

Ummm...Jeff, he wanted that to ONLY happen when the height and weight were entered ON THE SAME DATE.

"Must look eye". GROUP BY takes care of that requirement quite nicely.

Old Pedant · May 19th, 2009, 06:30 PM

SHEESH!

I *completely missed* your GROUP BY! Shame on me!

DOH and double DOH. Yes, a much better solution.

Jeff Moden · May 19th, 2009, 08:35 PM

Heh... not a problem.

For those following this thread, the method I used is nothing more than a "cross-tab". Cross-tabs are one of the fastest ways to "pivot" vertical data from an NVP table (Name Value pair) and it eliminates a lot of joins to do the same thing.