So far I have this code:
SELECT e.*,
case
when (t2.dx in (e.dx1,e.dx2,e.dx3)
AND (e.svcdate_form BETWEEN t2.pre_date AND t2.post_date))
then t2.proc2
else 0 end as trigger_cpt2
FROM eoc_example1 e
left outer join eoc_dx_lookup t2
on e.enrolid = t2.enrolid and e.svcdate_form = t2.svcdate_form;
I provided screenshots of how the tables look like: I have a mapping table known as t2 that looks like so:
and a table that I'm interested in creating the cases (I'm showing part of the entire table) known as e:
what I'm hoping to get is something like this:
This is my logic: If a row contains a dx1, dx2, or dx3 value found in my mapping table corresponding to a specific proc2 value AND contains a svcdate_form value within the pre_date and post_date columns, then label it as the proc2 value found in the mapping table.
When I run my code, I get a lot of values in trigger_cpt2 as 0 and apart from this, I get a total of around 170 rows when the actual amount of rows I'm interested in are 64 (based on the rows in table e).
Is there any way I could modify my code to get the output I'm looking for? Thanks so much in advance!
Aucun commentaire:
Enregistrer un commentaire