SQL code to check for specified health issue given several conditions
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'
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'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
If claim is different to code_value then better to use "EXISTS" rather than "CLAIM IN" as part of the where clause.
ASKER
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!!