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
 Update Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

taj
Starting Member

India
39 Posts

Posted - 09/21/2013 :  05:33:08  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.

I have one more table by name PATIENT_LAB_TESTS (TRANSACTION TABLE) with Test_ID,Test_Result,and Test_Status Columns.

In Master Table for a Test_ID 100(FASTING BLOOD SUGAR) if my Normal_Values range from 10-20(Low Value to High Value)

In my Transaction Table if my Test_Result Column is having 30 Result for Test_ID 100.

How to compare the Test_Result Column With Normal Values and Update the Test_Status Column in Transaction Table.


Below is the actual SP which am trying to work as per requrment.

Requirement:
1. If the value in the Results_V column is below the normal range(Low Value) then i should update the status_c column as 'L'
2. If the value in the Results_V column is above the normal range(High Value) then i should update the status_c column as 'H'
3. If the value in the Results_V column is between the normal range
then i should update the status_c column as ' ' (Empty Space)

Below is my SP

CREATE PROC[dbo].[PATIENT_UPDATE_LAB_RESULT]
@Test_Code VARCHAR(10),
@Patient_ID VARCHAR(10),
@Normal_value varchar(20) ,
@Result_v VARCHAR(10)
AS
BEGIN
UPDATE PLT SET PLT.STATUS_C =
CASE WHEN PLT.RESULTS_V < LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'L'
WHEN PLT.RESULTS_V > RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'H'
WHEN PLT.RESULTS_V BETWEEN LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) AND RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'N'
END
FROM PATIENT_LAB_TESTS PLT
INNER JOIN LAB_TEST_MASTER LMT ON LMT.TEST_CODE_N = @Test_Code
WHERE PATIENT_ID_N=@Patient_ID
END

Issues

1. Am not able to compare decimal values
2. The stored procedure retaining its last value of status column

allan8964
Posting Yak Master

247 Posts

Posted - 09/21/2013 :  14:07:54  Show Profile  Reply with Quote
I think your design for table LAB_TEST_MASTER is against the Normalization rules. According to 1NF rule no rows should contain values like 10-20, or firstName + lastName. That's why your codes in sp to pick up low and high values are ambigious, like this: < LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1). If you modify the master table to hold values range in 2 new columns like ValueMin float, ValueMax float, then you save 10 in ValueMin and 20 in ValueMax. Then in sp codes you can pick up data by CASE WHEN PLT.RESULTS_V < LMT.ValueMin ... something like this ... then you won't have decimal issue. By the way you should use float instead of decimal in your PLT table ...
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 09/21/2013 :  14:28:16  Show Profile  Reply with Quote
quote:
Originally posted by taj

Hi all,

I have a Table by name LAB_TEST_MASTER(MASTER TABLE)with Test_ID,Test_Name and Normal_Values columns.

I have one more table by name PATIENT_LAB_TESTS (TRANSACTION TABLE) with Test_ID,Test_Result,and Test_Status Columns.

In Master Table for a Test_ID 100(FASTING BLOOD SUGAR) if my Normal_Values range from 10-20(Low Value to High Value)

In my Transaction Table if my Test_Result Column is having 30 Result for Test_ID 100.

How to compare the Test_Result Column With Normal Values and Update the Test_Status Column in Transaction Table.


Below is the actual SP which am trying to work as per requrment.

Requirement:
1. If the value in the Results_V column is below the normal range(Low Value) then i should update the status_c column as 'L'
2. If the value in the Results_V column is above the normal range(High Value) then i should update the status_c column as 'H'
3. If the value in the Results_V column is between the normal range
then i should update the status_c column as ' ' (Empty Space)

Below is my SP

CREATE PROC[dbo].[PATIENT_UPDATE_LAB_RESULT]
@Test_Code VARCHAR(10),
@Patient_ID VARCHAR(10),
@Normal_value varchar(20) ,
@Result_v VARCHAR(10)
AS
BEGIN
UPDATE PLT SET PLT.STATUS_C =
CASE WHEN PLT.RESULTS_V < LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'L'
WHEN PLT.RESULTS_V > RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'H'
WHEN PLT.RESULTS_V BETWEEN LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) AND RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'N'
END
FROM PATIENT_LAB_TESTS PLT
INNER JOIN LAB_TEST_MASTER LMT ON LMT.TEST_CODE_N = @Test_Code
WHERE PATIENT_ID_N=@Patient_ID
END

Issues

1. Am not able to compare decimal values
2. The stored procedure retaining its last value of status column


Your description and the code does not seem to match. In your description, I see Test_ID is a column that is common to both tables. Yet, in your stored procedure, you are not even using that column. You should be joining on that column. Are there other columns in the table that you are using to join?

Perhaps this?
CREATE PROC[dbo].[PATIENT_UPDATE_LAB_RESULT] 
@Test_Code VARCHAR(10), 
@Patient_ID VARCHAR(10), 
@Normal_value varchar(20) , 
@Result_v VARCHAR(10) 
AS 
BEGIN 
UPDATE PLT SET PLT.STATUS_C = 
CASE WHEN PLT.RESULTS_V < LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'L' 
WHEN PLT.RESULTS_V > RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'H' 
WHEN PLT.RESULTS_V BETWEEN LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) AND RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'N' 
END 
FROM PATIENT_LAB_TESTS PLT
INNER JOIN LAB_TEST_MASTER LMT ON LMT.TEST_CODE_N = PLT.TEST_CODE
WHERE PATIENT_ID_N=@Patient_ID AND LMT.TEST_CODE_N = @Test_Code
END
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 09/21/2013 :  14:30:25  Show Profile  Reply with Quote
And, also forgot to mention the following:

While the physicians want to see normal ranges as 20-40 etc., it is not a good idea to keep the ranges as a string like that. You should have separate columns for low, high, very low, very high, panic low, panic high, or whatever else ranges you may want to apply.

Also, there are some tests for which the low/high range does not really apply. <100 might be normal for some tests, for example.
Go to Top of Page

taj
Starting Member

India
39 Posts

Posted - 09/23/2013 :  04:08:18  Show Profile  Reply with Quote
Hi James,

Thanks for your suggestion,
Can u please send the code for updating status column value after separating the columns for high and low.
Go to Top of Page

taj
Starting Member

India
39 Posts

Posted - 09/23/2013 :  06:24:37  Show Profile  Reply with Quote
quote:
Originally posted by James K

quote:
Originally posted by taj

Hi all,

I have a Table by name LAB_TEST_MASTER(MASTER TABLE)with Test_ID,Test_Name and Normal_Values columns.

I have one more table by name PATIENT_LAB_TESTS (TRANSACTION TABLE) with Test_ID,Test_Result,and Test_Status Columns.

In Master Table for a Test_ID 100(FASTING BLOOD SUGAR) if my Normal_Values range from 10-20(Low Value to High Value)

In my Transaction Table if my Test_Result Column is having 30 Result for Test_ID 100.

How to compare the Test_Result Column With Normal Values and Update the Test_Status Column in Transaction Table.


Below is the actual SP which am trying to work as per requrment.

Requirement:
1. If the value in the Results_V column is below the normal range(Low Value) then i should update the status_c column as 'L'
2. If the value in the Results_V column is above the normal range(High Value) then i should update the status_c column as 'H'
3. If the value in the Results_V column is between the normal range
then i should update the status_c column as ' ' (Empty Space)

Below is my SP

CREATE PROC[dbo].[PATIENT_UPDATE_LAB_RESULT]
@Test_Code VARCHAR(10),
@Patient_ID VARCHAR(10),
@Normal_value varchar(20) ,
@Result_v VARCHAR(10)
AS
BEGIN
UPDATE PLT SET PLT.STATUS_C =
CASE WHEN PLT.RESULTS_V < LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'L'
WHEN PLT.RESULTS_V > RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'H'
WHEN PLT.RESULTS_V BETWEEN LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) AND RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'N'
END
FROM PATIENT_LAB_TESTS PLT
INNER JOIN LAB_TEST_MASTER LMT ON LMT.TEST_CODE_N = @Test_Code
WHERE PATIENT_ID_N=@Patient_ID
END

Issues

1. Am not able to compare decimal values
2. The stored procedure retaining its last value of status column


Your description and the code does not seem to match. In your description, I see Test_ID is a column that is common to both tables. Yet, in your stored procedure, you are not even using that column. You should be joining on that column. Are there other columns in the table that you are using to join?

Perhaps this?
CREATE PROC[dbo].[PATIENT_UPDATE_LAB_RESULT] 
@Test_Code VARCHAR(10), 
@Patient_ID VARCHAR(10), 
@Normal_value varchar(20) , 
@Result_v VARCHAR(10) 
AS 
BEGIN 
UPDATE PLT SET PLT.STATUS_C = 
CASE WHEN PLT.RESULTS_V < LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'L' 
WHEN PLT.RESULTS_V > RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'H' 
WHEN PLT.RESULTS_V BETWEEN LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) AND RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'N' 
END 
FROM PATIENT_LAB_TESTS PLT
INNER JOIN LAB_TEST_MASTER LMT ON LMT.TEST_CODE_N = PLT.TEST_CODE
WHERE PATIENT_ID_N=@Patient_ID AND LMT.TEST_CODE_N = @Test_Code
END




Thanks for ur suggestion, If the use the above given code th status column is repeated by 'H' as values the values is not updating other than the 'H' value

Please suggest me some better answer.
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.19 seconds. Powered By: Snitz Forums 2000