I have two tables:
one contains Facility Outpatient data unique by Mem_id Claim_id and Svcdate
one contains Professional Outpatient data unique by Mem_id Claim_id and Svcdate
A Claim_id for a Facility Outpatient event is not the same Claim_id for the Professional
The data are specific to MRI’s (Mag Res Imaging).
I am joining the two tables to find pairs where the Facility event date equals the Professional event, Mem_id1 = Mem_id2 and SvcDate1 = SvcDate2
Hypothesis is MRI occurs first in facility and then the Professional event (reading the MRI and/or visiting with the patient to explain the findings) occurs.
Purpose is to look at total case, called casing logic, for the purposes of payment billing to come up with total cost for the case, so to speak.
The easy part is the same date join.
However, there are likely to be occasions when the professional event occurs b/w 1 to 5 days later.
I thought to perform a second join where the Mem_id’s were equal, however, where the Svcdates were not equal.
Of course this creates a table where a match due to <> dates can be the consequence of two separate pairs now in the first joined table come together to populate this table.
My question is, is there a way to query the originally joined table using this mismatched Svcdate table and the original 2 tables to find those few records where
the Facility Event and the Professional Event actually happened a few days apart?
I hope I have explained this thoroughly enough.
And of course, my approach may be completely screwy in which case alternatives are most certainly welcome.
Thanks my friends.