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.
Author |
Topic |
PUTTU PATIL
Starting Member
7 Posts |
Posted - 2013-09-24 : 05:45:58
|
Hi all,I have a Table by name LAB_TEST_MASTER(MASTER TABLE)with Test_ID,Test_Name and Normal_Values columns.Test Code Test Name Normal Value6 Blood Urea 20 - 4512 HBA1C Glycoslated Haemoglobin 4.0 - 6.086 Serum Creatinine 0.7 - 1.2 147 Fasting Blood Sugar 60 - 100 292 POST PRANDIAL BLOOD SUGAR 5 - 150I have one more table by name PATIENT_LAB_TESTS (TRANSACTION TABLE) with Patient_Id, Test_ID,Test_Result,and Test_Status Columns.Patient Id Test Code Result Status27924 6 51 NULL27924 12 5.5 NULL27924 86 0.9 NULL27924 147 55 NULL27924 292 59 NULLHow to compare the Test_Result Column With Normal Values and Update the Test_Status Column for multiple rows in Transaction Table.If my Test_Result value is less than Low Value of Normal_Values then i need to Update Test_Status as L andif Test_Result value is greater than High Value of Normal_Values then i need to Update Test_Status as H andif the Test_Result Value is in between Low and High Value of Normal_Values then i need to update a blank spaceHow to do this in sql server?Thank YouPUTTU PATIL |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-09-24 : 06:19:08
|
if you can change the LAB_TEST_MASTER table value to Low and High value it makes things much easierUPDATE pltSET Test_Status = CASE WHEN Test_Result < ltm.LowValue THEN 'L' WHEN Test_Result > ltm.HighValue THEN 'H' ELSE '' ENDFROM LAB_TEST_MASTER ltm INNER JOIN PATIENT_LAB_TESTS plt ON ltm.Test = plt.Test KH[spoiler]Time is always against us[/spoiler] |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
PUTTU PATIL
Starting Member
7 Posts |
Posted - 2013-09-24 : 07:16:10
|
Hi sir,The given code works for only 1 test code but i need to update status column for multiple test code. Below is the code which works only for one test code.UPDATE pltSET STATUS_C= CASE WHEN RESULTS_V < 20 THEN 'L' WHEN RESULTS_V > 45 THEN 'H' ELSE '' ENDFROM LAB_TEST_MASTER ltm INNER JOIN PATIENT_LAB_TESTS plt ON ltm.TEST_CODE_N = plt.TEST_CODE_N where PATIENT_ID_N = 27935 and ltm.TEST_CODE_N = 6PUTTU PATIL |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-25 : 07:52:29
|
quote: Originally posted by PUTTU PATIL Hi sir,The given code works for only 1 test code but i need to update status column for multiple test code. Below is the code which works only for one test code.UPDATE pltSET STATUS_C= CASE WHEN RESULTS_V < 20 THEN 'L' WHEN RESULTS_V > 45 THEN 'H' ELSE '' ENDFROM LAB_TEST_MASTER ltm INNER JOIN PATIENT_LAB_TESTS plt ON ltm.TEST_CODE_N = plt.TEST_CODE_N where PATIENT_ID_N = 27935 and ltm.TEST_CODE_N = 6PUTTU PATIL
Remove the hardcoded value for test code and you're all set------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|