SQL code to check for specified health issue given several conditions

karen1974
karen1974 used Ask the Experts™
on
Hello all,

I'm working on a code at the moment for a health plan that verifies if a pregnant woman meets certain defined criteria (e.g. is continuously enrolled in her first trimester and has a visit during the trimester), then she is considered "eligible" to be counted in a certain outcome measure; else, she is not.

I'd like to automate the process to then go through a set of decisions that will "filter" out if she then meets diagnostic criteria to assist in finding (or not) a claim to support the above defined criteria...does this make sense? I can be more specific and give more examples if needed.

I'm able to filter out who is eligible and not; where I need help is setting up the long listing of combinations that determine if she then may have a claim - and someone to check the logic and syntax.

There are 3 basic scenarios, each of which are mutually exclusive

1. Number 1 rule + 2A + 2B
2. Number 1 rule + 2A + 2c
3. Number 1 rule + 2D

Below is what I've started - the "PPC" table refers to a table we have where all codes for pregnancy saved for the measure so they don't have to be explicitly written out, just referenced by their type.

Many thanks in advance for your help!!

--code identifies member then need to run through conditions...

WHERE CLAIM NOT IN

(SELECT CODE_VALUE FROM  --Decision Rule 1  -- has to be any one code
table_cd = 'ppc-c' AND CODE_TYPE IN('CPT','CPT_CAT_II')
AND DESCR ='Decision Rule 1')

AND

(SELECT CODE_VALUE FROM  --Decision Rule 2 PART A  - combo A&B
table_cd ='ppc-c'
AND CODE_TYPE IN('CPT', 'REVENUE')
AND ProviderSpecialtyID ='obgy' -- required sp.
AND DESCR ='Decision Rule 2 Part A')

AND

(SELECT CODE_VALUE FROM  --Decision Rule 2 PART B  
table_cd ='ppc-c'
AND CODE_TYPE IN('CPT','DIAGNOSIS')
AND DESCR = 'Decision Rule 2 Part B')

OR
--Decision Rule 3 PART C -- a code for EACH of the 4 infections must be present
--for this component

(SELECT CODE_VALUE FROM   -- combo C&A
table_cd ='ppc-c'
AND DESCR ='Decision Rule 3 Part C TORCH Cytomegalovirus'
AND CODE_TYPE ='CPT' OR CODE_TYPE = 'LOINC'
AND
DESCR = 'Decision Rule 3 Part C TORCH Herpes simplex'
AND CODE_TYPE ='CPT' OR CODE_TYPE = 'LOINC'
AND
DESCR = 'Decision Rule 3 Part C TORCH Rubella'
AND CODE_TYPE ='CPT' OR CODE_TYPE = 'LOINC'
AND
DESCR = 'Decision Rule 3 Part C TORCH Toxoplasma'
AND CODE_TYPE ='CPT' OR CODE_TYPE = 'LOINC'

AND

(SELECT CODE_VALUE FROM  --Decision Rule 2 PART A  (combo)
table_cd ='ppc-c'
AND CODE_TYPE IN('CPT', 'REVENUE')
AND DESCR ='Decision Rule 2 Part A')

OR

(SELECT CODE_VALUE FROM   -- PART D is by itself
table_cd ='ppc-c'
AND DESCR LIKE 'Decision Rule 4 Part A%' -- Pt A & C must be met OR Pt C
AND CODE_TYPE ='CPT' OR CODE_TYPE = 'LOINC'
AND DESCR LIKE 'Decision Rule 4 Part B%'
OR
DESCR LIKE 'Decision Rule 4 Part C%'
AND CODE_TYPE = 'HCPCS'
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
OK, looks reasonably clean in terms of setting up.

Normally find these types of things easier to set up logically first then expand... e.g. those mutually exclusive rules do have some common ground, so why not group them ?

And is CLAIM really the same value as CODE_VALUE (hence the "in" statement) or is it the existance of those conditions that have to be met. What if the claim also existed in other combos not listed - would that then exclude e.g. what if not in 2A + (2B or 2C) then what if in 2D but also 2A (do we need to check not in 2A when checking in 2D) ?

e.g.

--1. Number 1 rule + 2A + 2B
--2. Number 1 rule + 2A + 2c
--3. Number 1 rule + 2D


WHERE CLAIM IN 
       (number 1 rule)
AND 
       (((2A)  AND  (2B OR 2C ))
OR
       (2D))

Open in new window


When we consider combinations like that an we have Rule1 (a common them regardless), then a combo of 2A, 2B, 2C, 2D we might need to consider the possibilities of (looking at singles and pairs - not every possible combo)

2A, 2B   = allow
2A, 2C   = allow
2A, 2D   = disallow
2B, 2C   = disallow
2B, 2D   = disallow
2C, 2D   = disallow
2A         = disallow
2B         = disallow
2C         = disallow
2D         = allow

Then sometimes we need to check explicitly for disallowing e.g. 2A,2D or 2B,2D or 2C,2D  unless of course any inclusion in 2D is auto inclusion regardless of anything else.

in which case, it might make more sense to restructure the pseudo code to make it more specific first and reduce the braket depth...

WHERE CLAIM IN 
       (number 1 rule)
AND 
       (2D
OR
       (2A  AND  (2B OR 2C ))

Open in new window


Note that the "IN" statement doesnt quite work in this case we need to either repeat the "IN", or, create the conditions as a subquery to gather a collection for the "IN" (and we might need to discuss that further).

Now, those combos above arent exactly represented in the current WHERE statement (e.g. code says decision rule part 4 and there is only 3 listed), so best to spell out what each part is...

-- Rule 1 (common to all)

(SELECT CODE_VALUE FROM  --Decision Rule 1  -- has to be any one code
table_cd = 'ppc-c' AND CODE_TYPE IN('CPT','CPT_CAT_II')
AND DESCR ='Decision Rule 1')

-- 2A

(SELECT CODE_VALUE FROM  --Decision Rule 2 PART A  - combo A&B
table_cd ='ppc-c'
AND CODE_TYPE IN('CPT', 'REVENUE') 
AND ProviderSpecialtyID ='obgy' -- required sp.
AND DESCR ='Decision Rule 2 Part A')

-- 2B

(SELECT CODE_VALUE FROM  --Decision Rule 2 PART B  
table_cd ='ppc-c'
AND CODE_TYPE IN('CPT','DIAGNOSIS') 
AND DESCR = 'Decision Rule 2 Part B') 

-- 2C  (beware of brackets)

(SELECT CODE_VALUE FROM   -- combo C&A
table_cd ='ppc-c' 
AND DESCR ='Decision Rule 3 Part C TORCH Cytomegalovirus'
AND CODE_TYPE ='CPT' OR CODE_TYPE = 'LOINC' 
AND 
DESCR = 'Decision Rule 3 Part C TORCH Herpes simplex'
AND CODE_TYPE ='CPT' OR CODE_TYPE = 'LOINC' 
AND
DESCR = 'Decision Rule 3 Part C TORCH Rubella'
AND CODE_TYPE ='CPT' OR CODE_TYPE = 'LOINC' 
AND
DESCR = 'Decision Rule 3 Part C TORCH Toxoplasma'
AND CODE_TYPE ='CPT' OR CODE_TYPE = 'LOINC' 

-- 2D (beware of brackets)

(SELECT CODE_VALUE FROM   -- PART D is by itself
table_cd ='ppc-c' 
AND DESCR LIKE 'Decision Rule 4 Part A%' -- Pt A & C must be met OR Pt C
AND CODE_TYPE ='CPT' OR CODE_TYPE = 'LOINC' 
AND DESCR LIKE 'Decision Rule 4 Part B%' 
OR
DESCR LIKE 'Decision Rule 4 Part C%'
AND CODE_TYPE = 'HCPCS' 

Open in new window


Now we really do need to resolve that "IN" statement, for now, we can simply repeat for each condition...

WHERE CLAIM IN 

       (SELECT CODE_VALUE FROM  --Decision Rule 1  -- has to be any one code
        table_cd = 'ppc-c' 
        AND CODE_TYPE IN('CPT','CPT_CAT_II')
        AND DESCR ='Decision Rule 1')

AND (CLAIM IN

        (SELECT CODE_VALUE FROM   -- PART D is by itself
         table_cd ='ppc-c' 
         AND (CODE_TYPE = 'HCPCS' AND DESCR LIKE 'Decision Rule 4 Part A%')
         OR  (CODE_TYPE = 'LOINC' AND DESCR LIKE 'Decision Rule 4 Part B%') 
         OR  (CODE_TYPE = 'HCPCS' AND DESCR LIKE 'Decision Rule 4 Part C%') )

OR (CLAIM IN

       (SELECT CODE_VALUE FROM  --Decision Rule 2 PART A  - combo A&B
        table_cd ='ppc-c'
        AND CODE_TYPE IN('CPT', 'REVENUE') 
        AND ProviderSpecialtyID ='obgy' -- required sp.
        AND DESCR ='Decision Rule 2 Part A')

        AND  

        (CLAIM IN 
                   (SELECT CODE_VALUE FROM  --Decision Rule 2 PART B  
                    table_cd ='ppc-c'
                    AND CODE_TYPE IN('CPT','DIAGNOSIS') 
                    AND DESCR = 'Decision Rule 2 Part B') 

         OR
         CLAIM IN 
                    (SELECT CODE_VALUE FROM   -- combo C&A
                     table_cd ='ppc-c' 
                     AND CODE_TYPE IN ('CPT','LOINC')
                     AND DESCR IN ('Decision Rule 3 Part C TORCH Cytomegalovirus'
                                  ,'Decision Rule 3 Part C TORCH Herpes simplex'
                                  ,'Decision Rule 3 Part C TORCH Rubella'
                                  ,'Decision Rule 3 Part C TORCH Toxoplasm') ))))

Open in new window


Now because they all have very similar structure and requirement (even though we cannot see the "FROM" table have assumed it is the same) we can further tune the WHERE clause... then you could also consider creating a VIEW or a subquery like :

SELECT CODE_VALUE,

               CASE WHEN CODE_TYPE IN('CPT','CPT_CAT_II')AND DESCR ='Decision Rule 1' THEN 'Y' 
                    ELSE 'N' 
               END  as Rule_1,

               CASE WHEN (CODE_TYPE = 'HCPCS' AND DESCR LIKE 'Decision Rule 4 Part A%') THEN 'Y'
                    WHEN (CODE_TYPE = 'LOINC' AND DESCR LIKE 'Decision Rule 4 Part B%') THEN 'Y'
                    WHEN (CODE_TYPE = 'HCPCS' AND DESCR LIKE 'Decision Rule 4 Part C%') THEN 'Y'
                    ELSE 'N' 
               END  as Rule_2D,

               CASE WHEN (CODE_TYPE IN('CPT', 'REVENUE') AND ProviderSpecialtyID ='obgy' AND DESCR ='Decision Rule 2 Part A' THEN 'Y'
                    ELSE 'N' 
               END  as Rule_2A,
 
               CASE WHEN (CODE_TYPE IN('CPT','DIAGNOSIS') AND DESCR = 'Decision Rule 2 Part B' THEN 'Y'
                    ELSE 'N' 
               END  as Rule_2B,

               CASE WHEN CODE_TYPE IN ('CPT','LOINC') AND DESCR IN ('Decision Rule 3 Part C TORCH Cytomegalovirus'
                                                                   ,'Decision Rule 3 Part C TORCH Herpes simplex'
                                                                   ,'Decision Rule 3 Part C TORCH Rubella'
                                                                   ,'Decision Rule 3 Part C TORCH Toxoplasm') THEN 'Y'
                    ELSE 'N' 
               END  as Rule_2C
  
        FROM   --source data--
        WHERE  table_cd = 'ppc-c'

Open in new window


And then select from that or link to that. Which is probably what I would do. Create as a view, and link to that view. If the rules change, then just have to modify the view and everything else stays the same.

But to really help, we need to see the table names and check those conditions, and check the relationship between CLAIM and CODE_VALUE etc... But hope that is a reasonable start for you.


Author

Commented:
Thank you so much -- just to have another person to see the logic and give input is a huge help.

Claim is different from code_value; however, you've given me a great framework here -- it's basically is there a claim and then is it or is it not in a set of conditions.

Much appreciation!!
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
No problems, hope it helps with the other person :)

If claim is different to code_value then better to use "EXISTS" rather than "CLAIM IN" as part of the where clause.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial