mardi 22 décembre 2020

SQL Case When based on another table and multiple conditions

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:

mapping

and a table that I'm interested in creating the cases (I'm showing part of the entire table) known as e:

data

what I'm hoping to get is something like this:

output

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