-- =============================================
-- Author:		Ivo Rakovac (rakovaci at who dot int), WHO Regional Office for Europe, Division of Noncommunicable Diseases and Promoting Health through the Life-course, WHO European Office for the Prevention and Control of Noncommunicable Diseases (NCD Office)
-- Create date: April 2017
-- Description:	SQL function to calculate the unconditional probability of dying between ages 30 and 69
-- =============================================
CREATE FUNCTION ProbabilityDying30_69 
(
	-- Parameters:
	-- Number of deaths from selected causes at ages 30-34,35-39,...,65-69 years
	@DeathsAge30_34 int,
	@DeathsAge35_39 int,
	@DeathsAge40_44 int,
	@DeathsAge45_49 int,
	@DeathsAge50_54 int,
	@DeathsAge55_59 int,
	@DeathsAge60_64 int,
	@DeathsAge65_69 int,
	-- Population in the age groups 30-34,35-39,...,65-69 years
	@PopAge30_34 float,
	@PopAge35_39 float,
	@PopAge40_44 float,
	@PopAge45_49 float,
	@PopAge50_54 float,
	@PopAge55_59 float,
	@PopAge60_64 float,
	@PopAge65_69 float


)
RETURNS float
AS
BEGIN
	-- m<-d/p
	Declare @MortAge30_34 float
	Set @MortAge30_34=@DeathsAge30_34/@PopAge30_34
	
	Declare @MortAge35_39 float
	Set @MortAge35_39=@DeathsAge35_39/@PopAge35_39
	
	Declare @MortAge40_44 float
	Set @MortAge40_44=@DeathsAge40_44/@PopAge40_44

	Declare @MortAge45_49 float
	Set @MortAge45_49=@DeathsAge45_49/@PopAge45_49

	Declare @MortAge50_54 float
	Set @MortAge50_54=@DeathsAge50_54/@PopAge50_54
	
	Declare @MortAge55_59 float
	Set @MortAge55_59=@DeathsAge55_59/@PopAge55_59

	Declare @MortAge60_64 float
	Set @MortAge60_64=@DeathsAge60_64/@PopAge60_64

	Declare @MortAge65_69 float
	Set @MortAge65_69=@DeathsAge65_69/@PopAge65_69

	-- q<-m*5/(1+m*2.5)

	Declare @QAge30_34 float
	Set @QAge30_34=@MortAge30_34*5.0/(1+@MortAge30_34*2.5)

	Declare @QAge35_39 float
	Set @QAge35_39=@MortAge35_39*5.0/(1+@MortAge35_39*2.5)

	Declare @QAge40_44 float
	Set @QAge40_44=@MortAge40_44*5.0/(1+@MortAge40_44*2.5)

	Declare @QAge45_49 float
	Set @QAge45_49=@MortAge45_49*5.0/(1+@MortAge45_49*2.5)

	Declare @QAge50_54 float
	Set @QAge50_54=@MortAge50_54*5.0/(1+@MortAge50_54*2.5)

	Declare @QAge55_59 float
	Set @QAge55_59=@MortAge55_59*5.0/(1+@MortAge55_59*2.5)

	Declare @QAge60_64 float
	Set @QAge60_64=@MortAge60_64*5.0/(1+@MortAge60_64*2.5)

	Declare @QAge65_69 float
	Set @QAge65_69=@MortAge65_69*5.0/(1+@MortAge65_69*2.5)

	-- res<-100*(1-(1-q[8])*(1-q[9])*(1-q[10])*(1-q[11])*(1-q[12])*(1-q[13])*(1-q[14])*(1-q[15]))
	
	Declare @res float
	Set @res =100.0*(1-(1-@QAge30_34)*(1-@QAge35_39)*(1-@QAge40_44)*(1-@QAge45_49)*(1-@QAge50_54)*(1-@QAge55_59)*(1-@QAge60_64)*(1-@QAge65_69))

	-- Return the result of the function
	RETURN @res

END
GO

