{"id":5824,"date":"2014-07-18T06:05:26","date_gmt":"2014-07-17T21:05:26","guid":{"rendered":"http:\/\/fujiitoshiki.com\/improvesociety\/?p=5824"},"modified":"2017-04-27T16:53:45","modified_gmt":"2017-04-27T07:53:45","slug":"how-to-create-scalar-function-of-sql-server-in-order-to-calculate-gnri","status":"publish","type":"post","link":"https:\/\/www.fujiitoshiki.com\/improvesociety\/?p=5824","title":{"rendered":"How to create scalar function of SQL Server in order to calculate GNRI?"},"content":{"rendered":"

Malnutrition in elder people increases the risk of death. Geriatric Nutrition Risk Index (GNRI) is the tool to detect malnutrition easily in hemodialysis patients, too. The definition is as following; <\/p>\n

\\displaystyle \\mathrm{GNRI} = 14.89\\times\\mathrm{Albumin (g\/dL)} + 41.7\\times\\frac{\\mathrm{Body Weight}}{\\mathrm{Ideal Body Weight}}<\/p>\n

where ideal body weight is given by multiplying 22 the square of height. But body weight should be replaced with ideal body weight if body weight is greater than ideal body weight. Then the second term is equal to 1. <\/p>\n

Table is defined as following procedure. It is based on the survey list of the Japanese Society for Dialysis Therapy in 2013. <\/p>\n

\r\nCREATE TABLE dbo.T_JSDT(\r\n\tID nchar(8) NOT NULL,\r\n\tDATE_Survey date NOT NULL,\r\n\tDiabetes nchar(1) NOT NULL,\r\n\tMyocardial_Infarction nchar(1) NOT NULL,\r\n\tCerebral_Hemorrhage nchar(1) NOT NULL,\r\n\tCerebral_Infarction nchar(1) NOT NULL,\r\n\tAmputation nchar(1) NOT NULL,\r\n\tFemoral_Fracture nchar(1) NOT NULL,\r\n\tEPS nchar(1) NOT NULL,\r\n\tHypertensive_Agents nchar(1) NOT NULL,\r\n\tSmoke nchar(1) NOT NULL,\r\n\tTherapy_Mode nchar(10) NOT NULL,\r\n\tCombination_PD nchar(1) NOT NULL,\r\n\tHistory_PD nchar(1) NOT NULL,\r\n\tTransplantation_COUNT nchar(1) NOT NULL,\r\n\tDIalysis_COUNT int NULL,\r\n\tDialysis_Duration int NULL,\r\n\tQB int NULL,\r\n\tHeight decimal(4, 1) NOT NULL,\r\n\tpreWeight decimal(4, 1) NOT NULL,\r\n\tpostWeight decimal(4, 1) NULL,\r\n\tpreBUN decimal(4, 1) NOT NULL,\r\n\tpostBUN decimal(4, 1) NULL,\r\n\tpreCre decimal(5, 2) NOT NULL,\r\n\tpostCre decimal(5, 2) NULL,\r\n\tAlbumin decimal(3, 1) NOT NULL,\r\n\tCRP decimal(4, 2) NOT NULL,\r\n\tCa decimal(3, 1) NOT NULL,\r\n\tIP decimal(3, 1) NOT NULL,\r\n\tHemoglobin decimal(3, 1) NOT NULL,\r\n\tTIBC decimal(3, 0) NULL,\r\n\tFe decimal(3, 0) NULL,\r\n\tFerritin decimal(5, 1) NULL,\r\n\tTCHO decimal(3, 0) NOT NULL,\r\n\tHDLC decimal(3, 0) NOT NULL,\r\n\tPTH_mode nchar(1) NOT NULL,\r\n\tPTH decimal(4, 0) NULL,\r\n\tHbA1c decimal(3, 1) NULL,\r\n\tGA decimal(3, 1) NULL,\r\n\tSBP decimal(3, 0) NOT NULL,\r\n\tDBP decimal(3, 0) NOT NULL,\r\n\tHR decimal(3, 0) NOT NULL,\r\n\tKtV_JSDT decimal(3, 2) NULL,\r\n\tnPCR_JSDT decimal(3, 2) NULL,\r\n\t[%CRE] decimal(4, 1) NULL,\r\n CONSTRAINT PK_T_JSDT PRIMARY KEY (ID, DATE_Survey)<\/pre>\n
We need height, body weight and albumin in the table. Execute following procedure to create function. <\/p>\n
\r\nCREATE FUNCTION Function_GNRI \r\n(@Albumin dec(3, 1), @Height dec(4, 1), @Weight dec(4, 1))\r\nRETURNS DEC(5, 2)\r\nAS\r\nBEGIN\r\n\tDECLARE @GNRI DEC(5,2)\r\n\tSELECT @GNRI = 14.89 * @Albumin + 41.7 * CASE WHEN @Weight > ( 22 * POWER(@Height\/100, 2)) THEN ( 22 * POWER(@Height\/100, 2)) ELSE @Weight END \/ ( 22 * POWER(@Height\/100, 2))\r\n\tRETURN @GNRI\r\nEND\r\n<\/pre>\n
Execute following query to calculate GNRI. <\/p>\n
\r\nWITH\tCTE\tAS\r\n(SELECT\tJ.ID\t\tAS ID\r\n\t,\tJ.DATE_Survey\tAS DATE_Survey\r\n\t,\tJ.Albumin\tAS ALB\r\n\t,\tJ.Height\tAS Height\r\n\t,\tCASE WHEN J.postWeight IS NULL THEN J.preWeight ELSE J.postWeight END\tAS Weight\r\n   FROM\tdbo.T_JSDT AS J\r\n),\tCTE_GNRI AS\r\n(SELECT\tCTE.ID\t\tAS ID\r\n\t,\tCTE.DATE_Survey\tAS DATE_Survey\r\n\t,\tdbo.Function_GNRI(CTE.ALB, CTE.Height, CTE.Weight)\tAS GNRI\r\nFROM\tCTE)\r\nSELECT\t*\tFROM\tCTE_GNRI;\r\n<\/pre>\n
Reference: Simplified nutritional screening tools for patients on maintenance hemodialysis<\/a><\/p>\n