Match unlinked data based on datetimes

Database
Enthusiast

Match unlinked data based on datetimes

I am trying to track the time between when a patient is discharged from a hospital to when they receive a follow-up telephone call. There is no direct link in the database (Teradata) between the hospital admission encounter and the folow-up telephone call so I would like to match the discharge date from the hospital admission to the most recent follow-up call after the discharge. However, a patient may have multiple admissions during a timeframe and multiple follow-up calls. If a follow-up call is matched with one previous discharge it cannot be matched with another discharge. The data looks similar to this:

LINE PAT_NAME DISCHARGE_DT FU_CALL_DT TIME_DIFF

1 Smith, Bob 9/1/2012 18:28 9/17/2012 13:34 22746

2 Smith, Bob 9/1/2012 18:28 9/21/2012 9:40 28272

3 Smith, Bob 9/1/2012 18:28 9/25/2012 14:24 34316

4 Smith, Bob 9/13/2012 17:37 9/17/2012 13:34 5517

5 Smith, Bob 9/13/2012 17:37 9/21/2012 9:40 11043

6 Smith, Bob 9/13/2012 17:37 9/25/2012 14:24 17087

7 Smith, Bob 9/20/2012 13:35 9/21/2012 9:40 1205

8 Smith, Bob 9/20/2012 13:35 9/25/2012 14:24 7249

In the case above, the records I need to return are lines 1,5, and 8. This matches the discharge with the most recent follow-up call that does not already have a match. The follow-up time can also be null in the cases where a patient did not receive a follow-up call so this scenario needs to be accounted for as well.

Any suggestions for how to do this would be appreciated. Please let me know if something is unclear or if you need more information.

Thanks,

Kevin

2 REPLIES
Junior Contributor

Re: Match unlinked data based on datetimes

Hi Kevin,

this is an interesting task, i think this is only possible using some recursion.

For this you need some starting point and your narration confuses me a bit...

I would start with the most recent FU_CALL_DT for the most recent DISCHARGE_DT to match the expected result.

But recursive queries in Teradata have some limitations like no OLAP/aggregates in the recursive part, so it's down to a SP using a loop like this:

CREATE VOLATILE TABLE vt, NO LOG AS tab WITH NO DATA ON COMMIT PRESERVE ROWS;

REPEAT
   INSERT INTO vt
   SELECT *
   FROM tab AS k
   WHERE NOT EXISTS
    (
      SELECT * FROM vt
      WHERE vt.PAT_NAME = k.PAT_NAME
      AND (vt.DISCHARGE_DT = k.DISCHARGE_DT OR vt.FU_CALL_DT = k.FU_CALL_DT)
    )
   QUALIFY ROW_NUMBER()
           OVER (PARTITION BY PAT_NAME
                 ORDER BY DISCHARGE_DT DESC, FU_CALL_DT DESC) = 1;
   
UNTIL ACTIVITY_COUNT = 0
END REPEAT;

When both tables have a matching PI on PAT_NAME this should be fast.

Regarding NULLs, you didn't tell how to account for :-)

Dieter

Enthusiast

Re: Match unlinked data based on datetimes

Hi Dieter,

Thanks for the solution. Due to the way our IT is set up we have not been able to use stored procedures. However, I am going to pursue this as it would open up other opportunities for us. Meanwhile, I will either have to resolve this using functions with arrays in Crystal Reports or see if the client will accept some other logic.

Thanks,

Kevin