T-SQL function to calculate breast cancer risk based on Gail 1999 model

create_ufn_RiskGail1999.sql

USE [UCI_Athena]
GO

/****** Object:  UserDefinedFunction [dbo].[ufn_RiskGail1999]    Script Date: 03/27/2012 13:45:05 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_RiskGail1999]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufn_RiskGail1999]
GO

USE [UCI_Athena]
GO

/****** Object:  UserDefinedFunction [dbo].[ufn_RiskGail1999]    Script Date: 03/27/2012 13:45:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Richard Kelly
-- Organization:	University of California
-- Create date:		3/27/2012
-- Description:		Function to calculate breast cancer risk based on Gail model described in:
--			Gail MH, Costantino JP, Bryant J, et al. Weighing the risks and the benefits of --			tamoxifen treatment for preventing breast cancer. J Natl Cancer Inst 1999; --			91:1829-1846. Vol. 91, No. 21, November 3, 1999
--			http://university.asco.org/sites/university.asco.org/files/ClassicReferences_Breast_General%20References_3..pdf
-- Usage:		SELECT dbo.ufn_RiskGail1999(<agemenarche>,<age>,<numbiopsies>,<ageflb>,<numrels>,
--			<atypiabiopsies>,<race>,<resulttype>)
-- =============================================

CREATE FUNCTION [dbo].[ufn_RiskGail1999]
(
	 @agemenarche int -- Age at menarche
	,@age int -- Age at counseling
	,@numbiopsies int -- Number of breast biopsies
	,@ageflb int -- Age at first live birth (-1 if nulliparous)
	,@numrels int -- Number of first degree relatives with breast cancer
	,@atypia int -- Atypical hyperplasia category
	-- 1 No biopsies
	-- 2 At least one biopsy and no atypical hyperplasia found in any biopsy specimen
	-- 3 No atypical hyperplasia found and hyperplasia status unknown for at least one biopsy specimen
	-- 4 Atypical hyperplasia found in at least one biopsy specimen
	,@race int -- Race category
	-- 1 Black
	-- 2 Not black
	,@resulttype int -- Result category
	-- 1 Relative risk
	-- 2 Projected 5-year risk
)
RETURNS real
AS
BEGIN
	DECLARE @factora real
	SELECT @factora = CASE
		WHEN @agemenarche >= 14 THEN 1
		WHEN @agemenarche >= 12 AND @agemenarche <=13  THEN 1.1
		WHEN @agemenarche < 12 THEN 1.21
		ELSE '@agemenarche out of bounds'
	END

	DECLARE @factorb real
	SELECT @factorb = CASE
		WHEN @age < 50 THEN CASE
			WHEN @numbiopsies =0 THEN 1
			WHEN @numbiopsies =1 THEN 1.7
			WHEN @numbiopsies >=2 THEN 2.88
			ELSE '@age<50 @numbiopsies out of bounds'
		END
		ELSE CASE
			WHEN @numbiopsies =0 THEN 1
			WHEN @numbiopsies =1 THEN 1.27
			WHEN @numbiopsies >=2 THEN 1.62
			ELSE '@age>=50 @numbiopsies out of bounds'
		END
	END

	DECLARE @factorc real
	SELECT @factorc = CASE
		WHEN @ageflb >0 AND @ageflb <20 THEN
			CASE
				WHEN @numrels =0 THEN 1
				WHEN @numrels =1 THEN 1
				WHEN @numrels >=2 THEN 6.8
				ELSE '@ageflb<20 @numrels out of bounds'
			END
		WHEN @ageflb >=20 AND @ageflb <=24 THEN
			CASE
				WHEN @numrels =0 THEN 1.24
				WHEN @numrels =1 THEN 2.68
				WHEN @numrels >=2 THEN 5.78
				ELSE '@ageflb<=24 @numrels out of bounds'
			END
		WHEN ( @ageflb >=25 AND @ageflb <=29 ) OR @ageflb =-1 THEN
			CASE
				WHEN @numrels =0 THEN 1.55
				WHEN @numrels =1 THEN 2.76
				WHEN @numrels >=2 THEN 4.91
				ELSE '@ageflb<=29 @numrels out of bounds'
			END
		WHEN @ageflb >=30 THEN
			CASE
				WHEN @numrels =0 THEN 1.93
				WHEN @numrels =1 THEN 2.83
				WHEN @numrels >=2 THEN 4.17
				ELSE '@ageflb>=30 @numrels out of bounds'
			END
		ELSE '@ageflb out of bounds'
	END

	DECLARE @factord real
	SELECT @factord = CASE
		WHEN @atypia =1 THEN 1
		WHEN @atypia =2 THEN .93
		WHEN @atypia =3 THEN 1
		WHEN @atypia =4 THEN 1.82
		ELSE '@atypia out of bounds'
	END
	
	DECLARE @baselinefiveyrpct real
	SELECT @baselinefiveyrpct = CASE
		WHEN @age >=20 and @age <=24 THEN
		CASE
			WHEN @race =1 THEN 0.014
			WHEN @race =2 THEN 0.012
			ELSE '@race out of bounds'
		END
		WHEN @age >=25 and @age <=29 THEN
		CASE
			WHEN @race =1 THEN 0.050
			WHEN @race =2 THEN 0.049
			ELSE '@race out of bounds'
		END
		WHEN @age >=30 and @age <=34 THEN
		CASE
			WHEN @race =1 THEN 0.120
			WHEN @race =2 THEN 0.134
			ELSE '@race out of bounds'
		END
		WHEN @age >=35 and @age <=39 THEN
		CASE
			WHEN @race =1 THEN 0.224
			WHEN @race =2 THEN 0.278
			ELSE '@race out of bounds'
		END
		WHEN @age >=40 and @age <=44 THEN
		CASE
			WHEN @race =1 THEN 0.310
			WHEN @race =2 THEN 0.450
			ELSE '@race out of bounds'
		END
		WHEN @age >=45 and @age <=49 THEN
		CASE
			WHEN @race =1 THEN 0.355
			WHEN @race =2 THEN 0.584
			ELSE '@race out of bounds'
		END
		WHEN @age >=50 and @age <=54 THEN
		CASE
			WHEN @race =1 THEN 0.416
			WHEN @race =2 THEN 0.703
			ELSE '@race out of bounds'
		END
		WHEN @age >=55 and @age <=59 THEN
		CASE
			WHEN @race =1 THEN 0.511
			WHEN @race =2 THEN 0.859
			ELSE '@race out of bounds'
		END
		WHEN @age >=60 and @age <=64 THEN
		CASE
			WHEN @race =1 THEN 0.562
			WHEN @race =2 THEN 1.018
			ELSE '@race out of bounds'
		END
		WHEN @age >=65 and @age <=69 THEN
		CASE
			WHEN @race =1 THEN 0.586
			WHEN @race =2 THEN 1.116
			ELSE '@race out of bounds'
		END
		WHEN @age >=70 and @age <=74 THEN
		CASE
			WHEN @race =1 THEN 0.646
			WHEN @race =2 THEN 1.157
			ELSE '@race out of bounds'
		END
		WHEN @age >=75 and @age <=79 THEN
		CASE
			WHEN @race =1 THEN 0.713
			WHEN @race =2 THEN 1.140
			ELSE '@race out of bounds'
		END
		WHEN @age >=80 and @age <=84 THEN
		CASE
			WHEN @race =1 THEN 0.659
			WHEN @race =2 THEN 1.006
			ELSE '@race out of bounds'
		END
	END

	DECLARE @riskrelative real
	SELECT @riskrelative = @factora * @factorb * @factorc * @factord
	DECLARE @riskfiveyrpct real
	SELECT @riskfiveyrpct = @riskrelative * @baselinefiveyrpct

	DECLARE @result real
	SELECT @result = CASE @resulttype WHEN 1 THEN @riskrelative WHEN 2 THEN @riskfiveyrpct END
	RETURN @result

END

GO

Changed @atypiabiopsies to @atypia. Suggestion by Patricia Donnellan.

Updated two risk values from 1989 values to 1999.