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 |
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 ' ' ENDFROM PATIENT_LAB_TESTS PLTJOIN LAB_TEST_MASTER LTM ON PLT.Test_ID = LTM .Test_ID[/code]--Chandu |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 ' ' ENDFROM PATIENT_LAB_TESTS PLTJOIN 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 ' ' ENDFROM PATIENT_LAB_TESTS PLTJOIN 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 testingUPDATE 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 ' ' ENDWHERE TEST_CODE_N=6AND PATIENT_ID_N=27901for '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, |
|
|
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 datayou could do likeUPDATE PLTSET Test_Status = CASE WHEN Test_Status < Low THEN 'L' WHEN Test_Status > High THEN 'H' WHEN Test_Status BETWEEN Low AND High THEN ' ' ENDFROM PATIENT_LAB_TESTS PLTJOIN (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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 ' ' ENDFROM PATIENT_LAB_TESTS PLTJOIN LAB_TEST_MASTER LTM ON PLT.Test_ID = LTM .Test_ID --Chandu |
|
|
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 rangethen i should update the status_c column as ' ' (Empty Space)Below is my SPCREATE 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 PLTINNER JOIN LAB_TEST_MASTER LMT ON LMT.TEST_CODE_N = @Test_Code WHERE PATIENT_ID_N=@Patient_ID ENDSHAIK |
|
|
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 rangethen i should update the status_c column as ' ' (Empty Space)Below is my SPCREATE 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 PLTINNER JOIN LAB_TEST_MASTER LMT ON LMT.TEST_CODE_N = @Test_Code WHERE PATIENT_ID_N=@Patient_ID ENDSHAIK
--Chandu |
|
|
|
|
|
|
|