Create a function with algorithm

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create a function with algorithm

Author

Topic

crazyco
Starting Member

30 Posts

Posted - 2011-09-15 : 04:33:19

Can anyone help in creating a function which will take 3 parameters, age, sex, and CRE_result and will return a calculated eGFR result based on the algorithm below:

eGFR = 175 * pwr((((CRE-3.08)/1.004)/88.4),-1.154) * MDRD
where:
IF $SEX = "M" THEN GEN = '1'
IF $SEX = "F" THEN GEN = '0.742'
MDRD = GEN * PWR (AGE,-0.203)

The Lab Number will be used to link across these tables:

BCMasterLab_from_Dec98
CHI
Lab_number
DoB
Sex
DT_sampled

BCMasterLab_results_from_Dec98
Lab_number
Test_code
Result
origResult
Comment

I've also to use a function called calc_age to calculate the age.

If anyone could help it would be much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 04:43:47

what all you pass as parameters for calc_age? assuming its only dob you need to do like


SELECT m.Lab_number,
eGFR = 175 * pwr((((Result-3.08)/1.004)/88.4),-1.154) * CASE SEX WHEN  'M' THEN 1 WHEN 'F' THEN 0.742 END * PWR (dbo.Calc_Age(DOB),-0.203)
FROM BCMasterLab_from_Dec98 m
INNER JOIN BCMasterLab_results_from_Dec98 r
ON r.Lab_number = m.Lab_number

dunno how this will perform with so many complex calculations and scalar function on select though!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

crazyco
Starting Member

30 Posts

Posted - 2011-09-15 : 06:55:24

Thank you for your help. I got it working using:

ALTER FUNCTION [dbo].[calc_egfr]

(
-- Add the parameters for the function here
@age int, @sex VARCHAR(10), @CRE_result int

)
RETURNS VARCHAR(10)
AS
BEGIN
-- Declare the return variable here
DECLARE @eGFR int

-- Add the T-SQL statements to compute the return value here

select @eGFR = (175 * power((((@CRE_result-3.08)/1.004)/88.4),-1.154)
* CASE @sex WHEN 'M' THEN 1 WHEN 'F' THEN 0.742 END * power(@age,-0.203))

-- Return the result of the function
RETURN @eGFR

END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 07:26:25

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

crazyco
Starting Member

30 Posts

Posted - 2011-09-15 : 08:50:32

Actually I've only got it half working, I've added in decimals as I'm getting the error "Msg 8134, Divide by zero error encountered" and not sure how to fix this.

ALTER FUNCTION [dbo].[calc_egfr]

(
-- Add the parameters for the function here
@age int, @sex VARCHAR(10), @CRE_result int

)
RETURNS VARCHAR(10)
AS
BEGIN
-- Declare the return variable here
DECLARE @eGFR decimal(5,2)

-- Add the T-SQL statements to compute the return value here

select @eGFR = (175 * power((((@CRE_result-3.08)/1.004)/88.4),-1.154)
* CASE @sex WHEN 'M' THEN 1 WHEN 'F' THEN 0.742 END * power(convert(decimal(5,2),@age ),-0.203))

-- Return the result of the function
RETURN @eGFR

END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 11:23:38

whats purpose of decimal? how can age be a decimal value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

crazyco
Starting Member

30 Posts

Posted - 2011-09-15 : 12:26:47

The age isn't the decimal, the result is a decimal (I've now changed it to a float). The outcome looks like this:

age eGFR
43 96.129
46 73.67

There is an age however that is 0 and I want it to state whilst age <= 0 then NULL and incorporate this into the function although not sure where it goes.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 12:48:28

[code]select @eGFR = (175 * power((((@CRE_result-3.08)/1.004)/88.4),-1.154)
* CASE @sex WHEN 'M' THEN 1 WHEN 'F' THEN 0.742 END * power(convert(decimal(5,2),CASE WHEN @age <= 0 then NULL else @age END),-0.203))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources