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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored Procedure For Upadate Statment

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 Value
6 Blood Urea 20 - 45
12 HBA1C Glycoslated Haemoglobin 4.0 - 6.0
86 Serum Creatinine 0.7 - 1.2
147 Fasting Blood Sugar 60 - 100  
292 POST PRANDIAL BLOOD SUGAR 5 - 150


I 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 Status
27924 6 51 NULL
27924 12 5.5 NULL
27924 86 0.9 NULL
27924 147 55 NULL
27924 292 59 NULL

How 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 and
if Test_Result value is greater than High Value of Normal_Values then i need to Update Test_Status as H and
if the Test_Result Value is in between Low and High Value of Normal_Values then i need to update a blank space

How to do this in sql server?

Thank You

PUTTU 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 easier

UPDATE plt
SET Test_Status = CASE WHEN Test_Result < ltm.LowValue
THEN 'L'
WHEN Test_Result > ltm.HighValue
THEN 'H'
ELSE ''
END
FROM LAB_TEST_MASTER ltm
INNER JOIN PATIENT_LAB_TESTS plt ON ltm.Test = plt.Test



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-24 : 06:57:19
Refer this link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=187923&SearchTerms=If,my,Test_Result,value,is,less,than,Low,Value

I think same query discussed

--
Chandu
Go to Top of Page

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 plt
SET STATUS_C= CASE WHEN RESULTS_V < 20
THEN 'L'
WHEN RESULTS_V > 45
THEN 'H'
ELSE ''
END
FROM 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 = 6




PUTTU PATIL
Go to Top of Page

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 plt
SET STATUS_C= CASE WHEN RESULTS_V < 20
THEN 'L'
WHEN RESULTS_V > 45
THEN 'H'
ELSE ''
END
FROM 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 = 6




PUTTU PATIL


Remove the hardcoded value for test code and you're all set

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -