Toad, Teradata, querying multiple tables

Toad Data Point & Toad Intelligence Central
1

Hi.

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
Outpatient event

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.

WHP

2

I think this might be the most straightforward solution, but there might be cases in the data where you could see some “cartesianing” because of what services might be rendered in some arbitrary order. You may want to start here and see if you find any anomalies that may require a different approach. Without some global value like a “case” ID that groups all the claims together, you might have a hard time with this.

SELECT A.MEM_ID, B.MEM_ID, A.CLAIM_ID, B.CLAIM_ID, A.SVCDATE, B.SVCDATE – PULL ALL COLUMNS FOR VALIDATION
FROM
FAC_OTPTNT_EVNT A,
PRO_OTPTNT_EVNT B
WHERE A.MEM_ID = B.MEM_ID
AND B.SVCDATE - A.SVCDATE BETWEEN 0 AND 5;

EDIT: One thing just popped into my head. How is the claim ID generated? Although they don’t relate between the tables, is there a way you can interpret a sequence. In other words, you know the professional service occurs after the facility event, so the claim ID is higher in sequence. Therefore, if you have claim IDs 1, 2, 3… n; you can assume that professional service with claim ID 10 came after facility event 7 for a given patient. If that’s the case, you could change the query to that below and add one more check to help reduce any Cartesian products you might find.

SELECT A.MEM_ID, B.MEM_ID, A.CLAIM_ID, B.CLAIM_ID, A.SVCDATE, B.SVCDATE – PULL ALL COLUMNS FOR VALIDATION
FROM
FAC_OTPTNT_EVNT A,
PRO_OTPTNT_EVNT B
WHERE A.MEM_ID = B.MEM_ID
AND B.SVCDATE - A.SVCDATE BETWEEN 0 AND 5
AND B.CLAIM_ID > A.CLAIM_ID;

The scenario I can’t seem to crack given the limited data is this:

Day 1: Patient has MRI 1
Day 2: Patient has MRI 2 and Professional Event 1
Day 3: Patient has Professional Event 2

3

Hi itlnstln, thanks for this advice, very helpful. I may have to re-frame my question in a new string. I will mark this answered for the time being thanks so much.
WHP