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
 Comparing the Values

Author  Topic 

taj
Starting Member

39 Posts

Posted - 2013-09-02 : 08:02:02
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.

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-02 : 08:12:49
[code]UPDATE
SET Test_Status =
CASE WHEN Test_Status < LEFT('10-20', CHARINDEX('-', '10-20')-1) THEN 'L'
WHEN Test_Status > RIGHT('10-20', CHARINDEX('-', '10-20')-1) THEN 'H'
WHEN Test_Status BETWEEN LEFT('10-20', CHARINDEX('-', '10-20')-1) AND RIGHT('10-20', CHARINDEX('-', '10-20')-1) THEN ' '
END
FROM PATIENT_LAB_TESTS PLT
JOIN LAB_TEST_MASTER LTM ON PLT.Test_ID = LTM .Test_ID[/code]

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-02 : 08:29:44
How is Normal values stored? Why not have it as a range as two columns? otherwise you might need to parse out string to get the individual values and then do the comparison.

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

taj
Starting Member

39 Posts

Posted - 2013-09-02 : 08:41:15
quote:
Originally posted by bandi

UPDATE 
SET Test_Status =
CASE WHEN Test_Status < LEFT('10-20', CHARINDEX('-', '10-20')-1) THEN 'L'
WHEN Test_Status > LEFT('10-20', CHARINDEX('-', '10-20')-1) THEN 'H'
WHEN Test_Status BETWEEN LEFT('10-20', CHARINDEX('-', '10-20')-1) AND RIGHT('10-20', CHARINDEX('-', '10-20')-1) THEN ' '
END
FROM PATIENT_LAB_TESTS PLT
JOIN LAB_TEST_MASTER LTM ON PLT.Test_ID = LTM .Test_ID


--
Chandu



quote:
Originally posted by bandi

UPDATE 
SET Test_Status =
CASE WHEN Test_Status < LEFT('10-20', CHARINDEX('-', '10-20')-1) THEN 'L'
WHEN Test_Status > LEFT('10-20', CHARINDEX('-', '10-20')-1) THEN 'H'
WHEN Test_Status BETWEEN LEFT('10-20', CHARINDEX('-', '10-20')-1) AND RIGHT('10-20', CHARINDEX('-', '10-20')-1) THEN ' '
END
FROM PATIENT_LAB_TESTS PLT
JOIN LAB_TEST_MASTER LTM ON PLT.Test_ID = LTM .Test_ID


--
Chandu



Hi Chandu,

Thanks for your Reply,

I have tried the below Piece of query for testing

UPDATE PATIENT_LAB_TESTS SET STATUS_C =
CASE WHEN RESULTS_V < LEFT('20-45', CHARINDEX('-', '20-45')-1) THEN 'L'
WHEN RESULTS_V > LEFT('20-45', CHARINDEX('-', '20-45')-1) THEN 'H'
WHEN RESULTS_V BETWEEN LEFT('20-45', CHARINDEX('-', '20-45')-1) AND RIGHT('20-45', CHARINDEX('-', '20-45')-1) THEN ' '
END
WHERE TEST_CODE_N=6
AND PATIENT_ID_N=27901

for 'L' and 'H' it works fine,But Where as when the Test_Result is in between 'L' and 'H' then it updates as 'H'..It should be Blank Space.

Thank You,
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-02 : 09:01:48
if its always of format N-N where N is numeric data
you could do like


UPDATE PLT
SET Test_Status =
CASE WHEN Test_Status < Low THEN 'L'
WHEN Test_Status > High THEN 'H'
WHEN Test_Status BETWEEN Low AND High THEN ' '
END
FROM PATIENT_LAB_TESTS PLT
JOIN (SELECT Test_ID,Test_Name,PARSENAME(REPLACE(Normal_Values,'-','.'),2) AS Low,PARSENAME(REPLACE(Normal_Values,'-','.'),1) AS High FROM LAB_TEST_MASTER) LTM ON PLT.Test_ID = LTM .Test_ID


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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-02 : 09:14:12
-- there was a typo.......
UPDATE 
SET Test_Status =
CASE WHEN Test_Status < LEFT('10-20', CHARINDEX('-', '10-20')-1) THEN 'L'
WHEN Test_Status > RIGHT('10-20', CHARINDEX('-', '10-20')-1) THEN 'H'
WHEN Test_Status BETWEEN LEFT('10-20', CHARINDEX('-', '10-20')-1) AND RIGHT('10-20', CHARINDEX('-', '10-20')-1) THEN ' '
END
FROM PATIENT_LAB_TESTS PLT
JOIN LAB_TEST_MASTER LTM ON PLT.Test_ID = LTM .Test_ID

--
Chandu
Go to Top of Page

taj
Starting Member

39 Posts

Posted - 2013-09-04 : 02:59:26
Below is the actual SP which am trying to make 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 > LEFT(@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

SHAIK
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-04 : 03:50:32
quote:
Originally posted by taj

Below is the actual SP which am trying to make 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

SHAIK



--
Chandu
Go to Top of Page
   

- Advertisement -