{"id":5810,"date":"2014-07-18T06:00:55","date_gmt":"2014-07-17T21:00:55","guid":{"rendered":"http:\/\/fujiitoshiki.com\/improvesociety\/?p=5810"},"modified":"2019-03-15T11:52:49","modified_gmt":"2019-03-15T02:52:49","slug":"post-5810","status":"publish","type":"post","link":"https:\/\/www.fujiitoshiki.com\/improvesociety\/?p=5810","title":{"rendered":"SQL Server\u306e\u30b9\u30ab\u30e9\u30fc\u5024\u95a2\u6570\u3068\u3057\u3066GNRI\u3092\u5b9a\u7fa9\u3059\u308b\u306b\u306f"},"content":{"rendered":"

\u3000\u9ad8\u9f62\u8005\u306b\u304a\u3051\u308b\u4f4e\u6804\u990a\u306f\u6b7b\u4ea1\u306e\u30ea\u30b9\u30af\u3092\u9ad8\u3081\u308b\u3053\u3068\u304c\u77e5\u3089\u308c\u3066\u3044\u307e\u3059\uff0eGeriatric Nutrition Risk Index (GNRI) \u306f\u4f4e\u6804\u990a\u3092\u7c21\u6613\u306b\u691c\u51fa\u3067\u304d\u308b\u30c4\u30fc\u30eb\u3067\u3059\uff0e\u5358\u306b\u9ad8\u9f62\u8005\u3060\u3051\u3067\u306f\u306a\u304f\uff0c\u7dad\u6301\u900f\u6790\u60a3\u8005\u306b\u304a\u3044\u3066\u3082\u6709\u7528\u3067\u3059\uff0e\u305d\u306e\u5b9a\u7fa9\u306f\u4ee5\u4e0b\u3067\u3059\uff0e<\/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

\u3053\u3053\u3067\u7406\u60f3\u4f53\u91cd (kg) \u306f\u8eab\u9577 (m) \u306e\u4e8c\u4e57\u306b 22 \u3092\u4e57\u3058\u3066\u5f97\u3089\u308c\u307e\u3059\uff0e\u305f\u3060\u3057\u5b9f\u4f53\u91cd\u304c\u7406\u60f3\u4f53\u91cd\u3092\u4e0a\u56de\u308b\u5834\u5408\u306b\u306f\uff0c\u5b9f\u4f53\u91cd\u3092\u7406\u60f3\u4f53\u91cd\u306b\u7f6e\u304d\u63db\u3048\u307e\u3059\uff0e\u3064\u307e\u308a\u7b2c 2 \u9805\u306e\u5206\u6570\u306f 1 \u306b\u7b49\u3057\u304f\u306a\u308a\u307e\u3059\uff0e<\/p>\n

\u3000\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9\u306f\u4ee5\u4e0b\u306e\u901a\u308a\u3067\u3042\u308b\u3068\u3057\u307e\u3059\uff0e\u65e5\u672c\u900f\u6790\u533b\u5b66\u4f1a\u7d71\u8a08\u8abf\u67fb\u7968\u306e 2013 \u5e74\u7248\u306b\u57fa\u3044\u3066\u3044\u307e\u3059\uff0e<\/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
\u3000\u4e0a\u8a18\u30c6\u30fc\u30d6\u30eb\u306e\u4e2d\u3067\u5fc5\u8981\u306a\u9805\u76ee\u306f\u8eab\u9577\uff0c\u4f53\u91cd\uff0c\u30a2\u30eb\u30d6\u30df\u30f3\u5024\u3067\u3059\uff0e\u4e0b\u8a18\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u3092\u5b9f\u884c\u3057\u3066\u95a2\u6570\u3092\u4f5c\u6210\u3057\u307e\u3059\uff0e\u5b9f\u4f53\u91cd\u304c\u7406\u60f3\u4f53\u91cd\u3092\u4e0a\u56de\u308b\u5834\u5408\u306b\u306f\u5b9f\u4f53\u91cd\u3092\u7406\u60f3\u4f53\u91cd\u306b\u7f6e\u304d\u63db\u3048\u308b\u3068\u3044\u3046\u6761\u4ef6\u306f CASE \u5f0f\u306e\u4e2d\u3067\u8a55\u4fa1\u3057\u307e\u3059\uff0e<\/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
\u3000\u4e0b\u8a18\u306e\u30af\u30a8\u30ea\u3092\u5b9f\u884c\u3057\u3066 GNRI \u3092\u6c42\u3081\u307e\u3059\uff0eCASE \u5f0f\u306e\u4e2d\u8eab\u306f\u900f\u6790\u5f8c\u4f53\u91cd\u304c\u7a7a\u6b04\u306e\u5834\u5408\u306f\u900f\u6790\u524d\u4f53\u91cd\u3067\u4ee3\u7528\u3059\u308b\u3068\u3044\u3046\u610f\u5473\u3067\u3059\uff0e<\/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
\u53c2\u7167\uff1a\u7dad\u6301\u900f\u6790\u60a3\u8005\u306e\u305f\u3081\u306e\u7c21\u6613\u6804\u990a\u30b9\u30af\u30ea\u30fc\u30cb\u30f3\u30b0\u30c4\u30fc\u30eb<\/a><\/p>\n