SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored Procedure For Upadate Statment
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PUTTU PATIL
Starting Member

India
7 Posts

Posted - 09/24/2013 :  05:45:58  Show Profile  Reply with Quote
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)

Singapore
17598 Posts

Posted - 09/24/2013 :  06:19:08  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 09/24/2013 :  06:57:19  Show Profile  Reply with Quote
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

India
7 Posts

Posted - 09/24/2013 :  07:16:10  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 09/25/2013 :  07:52:29  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000