Calculation using SQL server-CodePudding

Formula used This is the formula that I used to calculate
Expressed as a single equation:

eGFR = 142 x min(Scr/κ, 1)α x max(Scr/κ, 1)-1.200 x 0.9938Age x 1.012 [if female]

where:

Scr = serum creatinine in mg/dL
κ = 0.7  
α = -0.329  
min(Scr/κ, 1) is the minimum of Scr/κ or 1.0
max(Scr/κ, 1) is the maximum of Scr/κ or 1.0
Age (years)

Code that I tried

select 
   ROUND(141 * power(min(cast(40 as float) / 0.7 ) ,-0.329) * 
     power(max( cast(40 as float) / 0.7 * 1) ,- 1.209) * 0.993 *
     cast(42 as float) * 1.018 ,2) as kidney

Correct answer should be 123.

Any clue what I am missing in the query?

CodePudding user response：

Don't use MIN and MAX, those are aggregate (GROUP BY) functions. It would be good if SQL Server had GREATEST and LEAST functions, but it doesn't yet.

IIF(a < b, a, b) is LEAST(a,b).
IIF(a > b, a, b) is GREATEST(a,b).

Don't sweat the CASTs.

Make no assumptions about operator precedence ( x before , etc). Use parentheses.

Here's a rewrite of what I believe your formula should be. But I'm not getting the right answer yet either.

DECLARE @Scr AS INT   = 40;
DECLARE @k   AS FLOAT = 0.7;
DECLARE @a   AS FLOAT = -0.329;
DECLARE @age AS INT   = 42;
DECLARE @gender AS VARCHAR(MAX) = 'female';
DECLARE @factor AS FLOAT = CASE WHEN @gender = 'male' THEN 1.0 ELSE 1.012 END;
SELECT   142 
         (IIF(1 < @Scr/@k, 1, @Scr/@k) * @a)
       * (IIF(1 > @Scr/@k, 1, @Scr/@k))
       - (1.2 * 0.993 * @age * @factor);

I obviously misunderstood your formula, but this should get you started. Here's a fiddle.

CodePudding user response：

According to this site your equation is kinda wrong. Please be sure to add ^ to display the "to the power of".

DECLARE @Scr    AS FLOAT       = 40.0;
DECLARE @gender AS VARCHAR(10) = 'female';
DECLARE @age    AS SMALLINT    = 42;
DECLARE @k      AS FLOAT       = CASE WHEN @gender = 'male' THEN 0.9    ELSE 0.7    END;
DECLARE @a      AS FLOAT       = CASE WHEN @gender = 'male' THEN -0.302 ELSE -0.241 END;
DECLARE @factor AS FLOAT       = CASE WHEN @gender = 'male' THEN 1.000  ELSE 1.012  END;

SELECT 142 * POWER(IIF(1 < (@Scr/@k), 1, @Scr/@k), @a)
           * POWER(IIF(1 > (@Scr/@k), 1, @Scr/@k), -1.200)
           * POWER(0.9938, @age)
           * @factor;

Also this result is correct since it is calculated in mg/dl and your expected value is in umol/l

Here is another website (it may be german, but it should do the trick)