DEVHIDE
Login Or Sign up

SQL Case When statement relating 3 columns

102 Views Asked by At

I am using SQL Server to solve a scattered data issue. I have several VITAL SIGNS and I am working on a measure where it is MET when both Vital_12 & Vital_14 are performed. The NV values do not count as they mean "not recorded". Some vitals are logged in one column and not the other but I need both to meet each measure.

Goal: Capture the rows shown below where the PCR_ID has at least one non NV value entered into the columns "PulseOximetry" and "respiratory_rate" so I can identify if a healthcare measure was met. I am thinking row over partition but not sure.

Since one PCR_ID can have several Vital Signs done in one call, I need to count the ones where both where done but not the ones where just one is done and not the other. The EMT can log these in different row so that is my problem

see this sample

So, since it DOES count when both are filled out but not on the same line. I wrote this code

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Select   Distinct P.PCR_ID,case when Convert(Int, P.ePatient_15) is not null and Convert(int,P.ePatient_16) =2516007 then  
Convert(Int, P.ePatient_15 / 12) when  Convert(Int, P.ePatient_15) is not null and Convert(int,P.ePatient_16) =2516001 then  
Convert(Int, P.ePatient_15 / 365)when  Convert(Int, P.ePatient_15) is not null and Convert(int,P.ePatient_16) =2516003 then  
Convert(Int, P.ePatient_15 / 8,760)when  Convert(Int, P.ePatient_15) is not null and Convert(int,P.ePatient_16) =2516005 then  
Convert(Int, P.ePatient_15 / 524,600) Else P.ePatient_15 End As Age, P.ePatient_16 As Age_Units , CAST(LEFT(E.eTimes_03,10) AS DATE) As DateofCall, 
Case When S.eSituation_11 Like '%I50%' Then 'Heart Failure, Unspecified' 
    When S.eSituation_11 Like '%J00%' Then 'AcuteNasopharyngitis' 
    
    When S.eSituation_11 Like '%J05%' Then 'Acute obstructive laryngitis and epiglottitis'
    When S.eSituation_11 Like '%J18.9%' Then 'Pneumonia, unspecified organism' when S.eSituation_11 Like '%J20.9%' 
    then 'Pneumonia, unspecified organism'  when S.eSituation_11 Like '%F41.1%' 
    then 'Pneumonia, unspecified organism' when S.eSituation_11 Like '%J44.1%'
    then 'Generalized anxiety disorder' when S.eSituation_11 like '%J45.9%'then 'Chronic obstructive pulmonary disease (acute)exacerbation' when S.eSituation_11 Like '%J80%' 
    then 'Acute respiratory distress syndrome' when S.eSituation_11 Like '%J81%' then 'Pulmonary edema' when  S.eSituation_11 Like '%J93.9%' 
    then 'Pneumothorax, unspecified' when S.eSituation_11 Like '%J96%' then 'Respiratory failure, unspecified' when S.eSituation_11 like'%J96%'
    then 'Acute bronchospasm'  when S.eSituation_11 like '%G40.909' then 'Epilepsy, unspecified, not intractable, without status epilepticus' when S.eSituation_11  like '%R05%'
    then 'Cough' when S.eSituation_11  like '%R06%' then  'Abnormalities of breathing' when  S.eSituation_11 ='J98.01' then 'Acute bronchospasm'
    when  S.eSituation_11  like '%R09.2%' then 'Respiratory arrest'
    when  S.eSituation_11  like '%T14.9%' then 'Unspecified Injury'
    when S.eSituation_11  like '%T17.9%' then 'Foreign body in respiratory tract, part unspecified'  
    when S.eSituation_11 like '%R11.2' then 'Nausea with vomiting, unspecified' 
    when S.eSituation_11 like '%J11%' then 'Influenza due to unidentified influenza virus'Else S.eSituation_11 End As Primary_Impression,
    
    Case When S.eSituation_12 Like '%I50%' Then 'Heart Failure, Unspecified' 
    When S.eSituation_12 Like '%J00%' Then 'AcuteNasopharyngitis' When S.eSituation_12 Like '%J05%' Then 'Acute obstructive laryngitis and epiglottitis'
    When S.eSituation_12 Like '%J18.9%' Then 'Pneumonia, unspecified organism' when S.eSituation_12 Like '%J20.9%' 
    then 'Pneumonia, unspecified organism' when S.eSituation_12 Like '%J44.1%' 
    then 'Acute bronchitis, unspecified' when S.eSituation_12 like '%J45.9%'then 'Chronic obstructive pulmonary disease (acute)exacerbation' when S.eSituation_12 Like '%J80%' 
    then 'Acute respiratory distress syndrome' when S.eSituation_12 Like '%J81%' then 'Pulmonary edema' when  S.eSituation_12 Like '%J93.9%' 
    then 'Pneumothorax, unspecified' when S.eSituation_12 Like '%J96%' then 'Respiratory failure, unspecified' when S.eSituation_12 like'%J96%'
    then 'Acute bronchospasm'  when S.eSituation_12 like '%J98.01%' then 'Respiratory disorder, unspecified' when S.eSituation_12  like '%R05%'
    then 'Cough' when S.eSituation_12  like '%R06%' then  'Abnormalities of breathing' when  S.eSituation_12  like '%R09.2%' then 'Respiratory arrest'
    when  S.eSituation_12  like '%T14.9%' then 'Unspecified Injury'
    when S.eSituation_12 like '%R11.2' then 'Nausea with vomiting, unspecified' 
    when S.eSituation_12 Like '%J44.1%' then 'Generalized anxiety disorder' when S.eSituation_12
      like '%T17.9%' then 'Foreign body in respiratory tract, part unspecified' when S.eSituation_11  
    like '%J11%' then 'Influenza due to unidentified influenza virus' 
    when  S.eSituation_12 ='J98.01' then 'Acute bronchospasm'
    when S.eSituation_12 like '%G40.909' then 'Epilepsy, unspecified, not intractable, without status epilepticus' Else S.eSituation_12 End As Secondary_Impression,
     D.dAgency_03 as GMR_Agency, D.dAgency_02 as Agency_ID,
    P.ePatient_15 AS AGE, P.ePatient_16 As Age_Units,V.eVitals_12 As Pulse_Oximetry, V.eVitals_14 As Respiratory_Rate, 

    Case when (V.eVitals_12 is null and V.eVitals_14 is null) or (V.eVitals_12 LIKE '%NV%' and V.eVitals_14 Like '%NV%') 
    Then '-1' Else 'Include' END As Partial_Vitals
    


FROM eSituation S
  
  Join eResponse R ON  S.PCR_ID = R.PCR_ID
  Join ePatient P on S.PCR_ID = P.PCR_ID
  Join eVitals V  on S.PCR_ID = V.PCR_ID
  Join eTimes E on S.PCR_ID = E.PCR_ID
  left join dAgency D on S.PCR_ID = D.PCR_ID


 
Where 
        (/*eVitals_12 NOT LIKE '%NV=7701003%'
        AND V.eVitals_12 Not Like '%NV=7701001%'
        AND */eVitals_12 IS NOT NULL
        AND
        /*eVitals_14 NOT LIKE '%NV=7701003%' AnD
        eVitals_14 NOT LIKE '%NV=7701001%'
        AND */eVitals_14 IS NOT NULL )


AND
((
P.ePatient_15 NOT LIKE '%NV=%' AND P.ePatient_15 is not null  AND P.ePatient_16 is not null and P.ePatient_16 NOT LIKE '%NV=%'
        AND Convert(Int,P.ePatient_15) <= 18 AND  Convert(Int,P.ePatient_16) = 2516009 and not Convert (Int, P.ePatient_15) > 18
        
        )

        OR
        (
        P.ePatient_15 NOT LIKE '%NV=%' AND P.ePatient_16 NOT LIKE '%NV=%' and P.ePatient_15 IS NOT NULL AND P.ePatient_16 IS NOT NULL AND
         P.ePatient_16 in ('2516001','2516003','2516005','2516007')))

        AND
(
    S.eSituation_11 NOT LIKE '%NV=%' AND S.eSituation_11  IS NOT NULL AND S.eSituation_12 IS NOT NULL AND S.eSituation_12 NOT LIKE '%NV=%'
    AND S.eSituation_11 NOT LIKE '%RO%'

        AND S.eSituation_11 in ('I50.9','J00','J05','J18.9','J20.9','J44.1','J45.901','J80','J81','J93.9','J96','J98.01','R05','R0','R06')  

        OR

        S.eSituation_12 IN('I50.9','J00','J05','J18.9','J20.9','J44.1','J45.901','J80','J81','J93.9','J96','J98.01','R05','R06','R0', 'R09.2','T17.9'))
        
            
    AND
    (
        R.eResponse_05 NOT LIKE '%NV=%' AND R.eResponse_05 IS not Null AND
        R.eResponse_05 = '2205001' and R.eResponse_05 not like '%2205005%' 
    AND
    
         D.dAgency_03 is not null and D.dAgency_03 NOT LIKE '%NV=%'
  And E.eTimes_03 is not null AND E.eTimes_03 >= '2019-01-01' )

Review the results now here: (the NV values = not recorded)

PCR_ID  Pulse_Oximetry  Respiratory_Rate    Partial_Vitals
011acd11602446a08ac919375c36f9ff    98  30  Include
011acd11602446a08ac919375c36f9ff    99  30  Include
011acd11602446a08ac919375c36f9ff    99  34  Include
011acd11602446a08ac919375c36f9ff    99  44  Include
0180f79087774621ba273d09929a71df    86  52  Include
0180f79087774621ba273d09929a71df    97  18  Include
0180f79087774621ba273d09929a71df    97  22  Include
0180f79087774621ba273d09929a71df    98  15  Include
01842d8cfe3541049425fea0555c672d    100 30  Include
01842d8cfe3541049425fea0555c672d    99  NV=7701003  Include
01c2c338c40d41a585099b545f89b226    100 30  Include
01c2c338c40d41a585099b545f89b226    90  30  Include
01c2c338c40d41a585099b545f89b226    98  30  Include
01c2c338c40d41a585099b545f89b226    100 30  Include
01c2c338c40d41a585099b545f89b226    90  30  Include
01c2c338c40d41a585099b545f89b226    98  30  Include
02b795683d804bd18936495fc7d1819d    87  20  Include
02b795683d804bd18936495fc7d1819d    96  18  Include
02b795683d804bd18936495fc7d1819d    97  20  Include
02b795683d804bd18936495fc7d1819d    97  24  Include
02f37ecf0030424caafde5e85e71bc76    88  40  Include
02f37ecf0030424caafde5e85e71bc76    94  40  Include
0334ba78e86343f4946edf4aea0214a7    100 16  Include
0334ba78e86343f4946edf4aea0214a7    100 19  Include
0334ba78e86343f4946edf4aea0214a7    99  26  Include
0334ba78e86343f4946edf4aea0214a7    NV=7701003  NV=7701003  -1
03bd5ca76efa4152ae1e876452c30e19    98  28  Include
03ec5e9e98aa4f909b3aa9d69cef8922    98  40  Include
03ec5e9e98aa4f909b3aa9d69cef8922    98  42  Include
03ec5e9e98aa4f909b3aa9d69cef8922    98  48  Include
03ec5e9e98aa4f909b3aa9d69cef8922    98  40  Include
03ec5e9e98aa4f909b3aa9d69cef8922    98  42  Include
03ec5e9e98aa4f909b3aa9d69cef8922    98  48  Include
0427d5a2e273425fa724f64a56fb93ea    100 36  Include
0427d5a2e273425fa724f64a56fb93ea    100 40  Include
0427d5a2e273425fa724f64a56fb93ea    NV=7701003  NV=7701003  -1
043e2bcd23684213afdae0059ecea344-9890A20200824211443334 100 40  Include
043e2bcd23684213afdae0059ecea344-9890A20200824211443334 95  40  Include
043e2bcd23684213afdae0059ecea344-9890A20200824211443334 96  42  Include
043e2bcd23684213afdae0059ecea344-9890A20200824211443334 97  38  Include
043e2bcd23684213afdae0059ecea344-9890A20200824211443334 NV=7701003  NV=7701003  -1
043e2bcd23684213afdae0059ecea344-9890A20200825100942340 100 40  Include
043e2bcd23684213afdae0059ecea344-9890A20200825100942340 95  40  Include
043e2bcd23684213afdae0059ecea344-9890A20200825100942340 96  42  Include
043e2bcd23684213afdae0059ecea344-9890A20200825100942340 97  38  Include
043e2bcd23684213afdae0059ecea344-9890A20200825100942340 NV=7701003  NV=7701003  -1
04554aa5c05246eba3512245fe69c331    97  34  Include
04554aa5c05246eba3512245fe69c331    97  NV=7701003  Include
04554aa5c05246eba3512245fe69c331    98  33  Include
04946d009336477a98833091604eaed0    90  22  Include
04946d009336477a98833091604eaed0    95  15  Include
04946d009336477a98833091604eaed0    98  18  Include
04fda20a5830496a89ef35ddc13b114d    95  18  Include
04fda20a5830496a89ef35ddc13b114d    98  18  Include
05090120b3374c1f850ec263b23e5e16-238298836117918    98  22  Include
050f82a8797a47e694faa6a5e611a544    97  20  Include
050f82a8797a47e694faa6a5e611a544    98  28  Include
050f82a8797a47e694faa6a5e611a544    99  32  Include
05b71453d3464835b9129c67345b3827    92  NV=7701003  Include
05b71453d3464835b9129c67345b3827    95  32  Include
06018fe90348405a8991cb5c5a3fb66d    98  55  Include
06018fe90348405a8991cb5c5a3fb66d    98  58  Include
06cd52b794504ca3b8d95e3740a10748    97  50  Include
06cd52b794504ca3b8d95e3740a10748    98  40  Include
06cd52b794504ca3b8d95e3740a10748    99  50  Include
07b4b23d7c434fdd9b1725251b4d38af    97  28  Include
07b4b23d7c434fdd9b1725251b4d38af    99  28  Include
084e300971cd4493b8d2b8d205dd8f8c    100 18  Include
091e8704652b411da33d35afcbc6c224    100 30  Include
091e8704652b411da33d35afcbc6c224    99  30  Include
098718fc059547039bec78dd0965bbf2    97  NV=7701003  Include
098718fc059547039bec78dd0965bbf2    98  NV=7701003  Include
09d1b129d388428d923517853581eb7d    99  40  Include
09d1b129d388428d923517853581eb7d    99  60  Include
09d1b129d388428d923517853581eb7d    99  40  Include
09d1b129d388428d923517853581eb7d    99  60  Include
09dabde283a6434f976d1290c65b7de2    95  23  Include
09dabde283a6434f976d1290c65b7de2    99  24  Include
0a8ac77f9c104d1788b25fc4bb97fee9    100 26  Include
0a8ac77f9c104d1788b25fc4bb97fee9    95  30  Include
0ad4d67c75b64266a85f226ab4636983    96  NV=7701003  Include
0ad4d67c75b64266a85f226ab4636983    98  NV=7701003  Include
0ad4d67c75b64266a85f226ab4636983    99  16  Include
0ad4d67c75b64266a85f226ab4636983    99  NV=7701003  Include
0ad4d67c75b64266a85f226ab4636983    NV=7701003  NV=7701003  -1
0ad4d67c75b64266a85f226ab4636983    96  NV=7701003  Include
0ad4d67c75b64266a85f226ab4636983    98  NV=7701003  Include
0ad4d67c75b64266a85f226ab4636983    99  16  Include
0ad4d67c75b64266a85f226ab4636983    99  NV=7701003  Include
0ad4d67c75b64266a85f226ab4636983    NV=7701003  NV=7701003  -1
0aec4b6aa0784326a26fc0f0f34598ef    97  20  Include
0b2ebf66a9a2441a81429e5f78f5a11f    99  24  Include
0b70878094944947a5873d446e6e5d58    54  23  Include
0b70878094944947a5873d446e6e5d58    59  18  Include
0b70878094944947a5873d446e6e5d58    61  19  Include
0b70878094944947a5873d446e6e5d58    68  18  Include
0b70878094944947a5873d446e6e5d58    68  32  Include
0b70878094944947a5873d446e6e5d58    77  17  Include
0b70878094944947a5873d446e6e5d58    91  24  Include
0b70878094944947a5873d446e6e5d58    93  25  Include
0b70878094944947a5873d446e6e5d58    NV=7701003  NV=7701003  -1
0ba1bba70415458ebd5b0edb00917516    100 27  Include
0ba1bba70415458ebd5b0edb00917516    89  28  Include
0ba1bba70415458ebd5b0edb00917516    97  36  Include
0ba9dc304d574859b565a91fdadef619    97  20  Include
0ba9dc304d574859b565a91fdadef619    98  14  Include
0ba9dc304d574859b565a91fdadef619    99  16  Include
0ba9dc304d574859b565a91fdadef619    99  NV=7701003  Include
0ba9dc304d574859b565a91fdadef619    97  20  Include
0ba9dc304d574859b565a91fdadef619    98  14  Include
0ba9dc304d574859b565a91fdadef619    99  16  Include
0ba9dc304d574859b565a91fdadef619    99  NV=7701003  Include
0beb24df85ab4fd6b21b28f0d1d93eb7-9491A20200826151457334 100 24  Include
0beb24df85ab4fd6b21b28f0d1d93eb7-9491A20200826151457334 NV=7701003  NV=7701003  -1
0beb24df85ab4fd6b21b28f0d1d93eb7-9491A20200826151914340 100 24  Include
0beb24df85ab4fd6b21b28f0d1d93eb7-9491A20200826151914340 NV=7701003  NV=7701003  -1
0c0c69f7e97340678159504a9ad3dce8    96  48  Include
0c0c69f7e97340678159504a9ad3dce8    97  44  Include
0c0c69f7e97340678159504a9ad3dce8    98  30  Include
0c0c69f7e97340678159504a9ad3dce8    98  44  Include
0c27c2a11f3d4fd5a4b4103e46ecf786    100 28  Include
0c6177e09bc2442fa3c6ed023b41b755    98  30  Include
0d1d10533934412ebd2d1543c34b9b4e    89  30  Include
0d1d10533934412ebd2d1543c34b9b4e    92  NV=7701003  Include
0d1d10533934412ebd2d1543c34b9b4e    93  NV=7701003  Include
0d1d10533934412ebd2d1543c34b9b4e    94  NV=7701003  Include
0d1d10533934412ebd2d1543c34b9b4e    95  NV=7701003  Include
0d1d10533934412ebd2d1543c34b9b4e    97  NV=7701003  Include
0d1d10533934412ebd2d1543c34b9b4e    98  NV=7701003  Include
0d2d9ec024384584b01971c994746276    100 NV=7701003  Include
0d2d9ec024384584b01971c994746276    98  18  Include
0d3085f20a2741379d8570a52c279cca    100 NV=7701003  Include
0d3085f20a2741379d8570a52c279cca    98  32  Include
0d3085f20a2741379d8570a52c279cca    NV=7701003  32  Include
0db5169e04e6415e9044283d8b632984    100 69  Include
0db5169e04e6415e9044283d8b632984    97  58  Include
0db5169e04e6415e9044283d8b632984    97  64  Include
0db5169e04e6415e9044283d8b632984    97  76  Include
0e9929a9b20542579a2eeb911166facf    100 NV=7701003  Include
0e9929a9b20542579a2eeb911166facf    99  26  Include
0eb0db228a0d4941b0864e5a1105279a    100 12  Include
0eb0db228a0d4941b0864e5a1105279a    100 14  Include
0eb0db228a0d4941b0864e5a1105279a    100 NV=7701003  Include
0eb0db228a0d4941b0864e5a1105279a    48  NV=7701003  Include
0eb0db228a0d4941b0864e5a1105279a    83  13  Include
0eb0db228a0d4941b0864e5a1105279a    98  11  Include
0eb0db228a0d4941b0864e5a1105279a    99  25  Include
0ec7b7bd3d9b4289aa85674811cb8b04    98  26  Include
0ec7b7bd3d9b4289aa85674811cb8b04    NV=7701003  28  Include
0f610287eac042e083cf53b1369234e1    97  20  Include
0f610287eac042e083cf53b1369234e1    97  NV=7701003  Include
0f610287eac042e083cf53b1369234e1    98  26  Include
0f610287eac042e083cf53b1369234e1    98  NV=7701003  Include
0f610287eac042e083cf53b1369234e1    99  NV=7701003  Include
0f610287eac042e083cf53b1369234e1    97  20  Include
0f610287eac042e083cf53b1369234e1    97  NV=7701003  Include
0f610287eac042e083cf53b1369234e1    98  26  Include
0f610287eac042e083cf53b1369234e1    98  NV=7701003  Include
0f610287eac042e083cf53b1369234e1    99  NV=7701003  Include
0f7ad14ef8a3431aa6589790fbfdf0bc    100 24  Include
0f7ad14ef8a3431aa6589790fbfdf0bc    100 26  Include
0fbdbef9614d4a3eaac37cda1c3738c3    95  28  Include
0fbdbef9614d4a3eaac37cda1c3738c3    96  28  Include
0fe72efced9e41fabea66a0bd49b4be2    100 26  Include
0fe72efced9e41fabea66a0bd49b4be2    93  NV=7701003  Include
0fe72efced9e41fabea66a0bd49b4be2    95  26  Include
0fe72efced9e41fabea66a0bd49b4be2    95  35  Include
0fe72efced9e41fabea66a0bd49b4be2    NV=7701003  16  Include
0fe72efced9e41fabea66a0bd49b4be2    NV=7701003  31  Include
0fe72efced9e41fabea66a0bd49b4be2    NV=7701003  NV=7701003  -1
100a0d37b4284218a2659621957c406d    99  20  Include
101165b34dad46f4b0986b4e3046d96d    100 22  Include
101165b34dad46f4b0986b4e3046d96d    100 24  Include
1021ee5be07a45cfa2ca25d7d7169f13    98  28  Include
1021ee5be07a45cfa2ca25d7d7169f13    99  26  Include
1057df631f3b4ed4a33f8399172ffc9e    98  18  Include
1057df631f3b4ed4a33f8399172ffc9e    98  25  Include
1057df631f3b4ed4a33f8399172ffc9e    98  18  Include
1057df631f3b4ed4a33f8399172ffc9e    98  25  Include
10e38582171b48f394d89b35f33fb6dd    96  18  Include
10f0103c9a0b473bac1a4b6f1a5dba67    100 16  Include
10f0103c9a0b473bac1a4b6f1a5dba67    96  14  Include
10f0103c9a0b473bac1a4b6f1a5dba67    97  18  Include
10f0103c9a0b473bac1a4b6f1a5dba67    98  13  Include
10f0103c9a0b473bac1a4b6f1a5dba67    98  18  Include
10f0103c9a0b473bac1a4b6f1a5dba67    99  14  Include

I wrote a basic `CASE WHEN` statement here:

    CASE 
        WHEN (V.eVitals_12 IS NULL AND V.eVitals_14 IS NULL) OR
             (V.eVitals_12 LIKE '%NV%' AND V.eVitals_14 LIKE '%NV%')
            THEN '-1' 
            ELSE 'Include' 
    END As Partial_Vitals, 
    COUNT(*) AS Count

I need to apply this to each PCR_ID.

sql sql-server t-sql case-when
Original Q&A
1

There are 1 best solutions below

2
Caius Jard On

Ok, it doesn't help that your example data is a screenshot with no column headers but I think the pcr id is on the very left and the pulse ox and rr values are on the right

You seem to be saying that you want all rows of data but only where there is at least one value that doesn't start with nv in each of the rightmost columns

I'm not going to try and replicate your massively complex sql on a cellphone, but here is a cut down version:

SELECT * 
FROM (
  SELECT *,
      COUNT(CASE WHEN pulseox NOT LIKE 'NV%' THEN 1 END) OVER(PARTITION BY pcr_id) as count_ox,
      COUNT(CASE WHEN resrate NOT LIKE 'NV%' THEN 1 END) OVER(PARTITION BY pcr_id) as count_rr
)x
WHERE count_ox > 0 AND count_rr > 0

The case when has no ELSE and thus an NV value will become NULL and not be counted. The 1 doesn't have to be 1 - it can be any non null value to be counted as 1. The inner query counts the number of non null values for each of two columns and the outer query demands that those counts be greater than 0 in both cases. Thus you get all rows of data but only for patients who have had at least 1 ox and 1 rr reading

Related Questions in SQL

Related Questions in SQL-SERVER

Related Questions in T-SQL

Related Questions in CASE-WHEN

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json

Popular Questions

.

Copyright © 2021 Jogjafile Inc.