Toad, Teradata, SQL, CASE Statements and multiple "following" statement

Hi.

I have a problem with my data that is causing the results of my script below
to produce inaccurate results.

In some instances “by Mem_id” I have a record where the next following record is
a visit record which is what I am looking for but the visit occured in between the Admit & Discharge dates and I need to eveluate the next subsequent record to it.

(FYI, This is simply a visit that occured in the hospital during the admit and I am looking
for post discharge visits within 14 days of the discharge).

Currently I have:

WHEN “Office Visit Date” BETWEEN Dte_Admit_id AND Dte_Disch_id
THEN NULL

But this is only part of what I need.

Is there some way to modify my logic here to to do something like:

WHEN “Office Visit Date” BETWEEN Dte_Admit_id AND Dte_Disch_id then ignore that date and look at the next record chronologically “by Mem_id” and flag accordingly,

Maybe a Next_NextSvcPlace_id = 36 AND Stay_id <> -2 AND “Day Diff” BETWEEN 0 and 15 AND NEXT “NextSvcPlace_id” = 37
THEN 0.

Any thoughts would be appreciated, thank you.

WHP

CREATE TABLE ar_asrh_dm_etl_beacon.Flg_OV_Algo1a3 AS
(SELECT Claim_id, Mem_id, Dte_SvcFrom_id AS “DOS or Admit”, Dte_Disch_id AS discharge,

MIN (Dte_SvcFrom_id) over (partition BY Mem_id ORDER BY Mem_id, Dte_SvcFrom_id
ROWS between 1 following and 1 following) AS “Office Visit Date”,

MIN (Dte_SvcFrom_id) over (partition BY Mem_id ORDER BY Mem_id, Dte_SvcFrom_id
ROWS between 1 following and 1 following) - Dte_Disch_id AS "Day Diff",

MIN (SvcPlace_id) over (partition BY Mem_id ORDER BY Mem_id, Dte_SvcFrom_id
ROWS between 1 following and 1 following) /- Dte_Disch_id/ AS “NextSvcPlace_id”,

CASE
WHEN SvcPlace_id = 36 AND Stay_id <> -2 AND “Day Diff” BETWEEN 0 and 15 AND “NextSvcPlace_id” = 37
THEN 0
WHEN PatStat_id IN (3, 8, 11, 12) and SvcPlace_id = 36
THEN 0
WHEN SvcPlace_id = 36 AND Stay_id <> -2 AND “NextSvcPlace_id” = 36
THEN NULL
WHEN SvcPlace_id = 37 and Stay_id = -2
THEN NULL
WHEN “Office Visit Date” BETWEEN Dte_Admit_id AND Dte_Disch_id
THEN NULL
WHEN “Day Diff” < 0
THEN NULL
ELSE 1
END AS “14DyOfficeVisitFLG”, Dte_SvcFrom_id, Dte_Admit_id, Dte_Disch_id,
Stay_id, SvcPlace_id, ClaimCategory_id, Dx_Cd, Dx_Dl, PatStat_dl, PatStat_id,
ProvRend_LastName, ProvRend_Id, MSDRG_id, ProvSpc_id, ProvSpc_ds

FROM ar_asrh_dm_etl_beacon.Flg_OV_Algo1a2)

WITH DATA;

Hi All.

I should have posted an example of the problem I am trying to solve.

Here are data after my “Following” script is run.

You may need to copy and paste into excel?

You can see that the 7th record’s DOS was between record #6’s DOS & Discharge.

The same for the 14th & 15th.

Notice that recods 2 & 3 work correctly as do 19 & 20, and 21 & 22.

Thanks for any advice.

WHP

Claim_id Mem_id DOS or Admit discharge Office Visit Date Day Diff NextSvcPlace_id 14DyOfficeVisitFLG SvcPlace_id
948472 3057 1/2/2010 1/1/0001 0 36 37
1005821 3057 1/24/2010 2/4/2010 2/9/2010 5 37 0 36
1266311 3057 2/9/2010 1/1/0001 0 37 37
2301387 3057 2/16/2010 1/1/0001 0 37 37
2302450 3057 2/23/2010 1/1/0001 0 36 37
2304418 3057 2/25/2010 3/5/2010 3/1/2010 0 37 36
1533649 3057 3/1/2010 1/1/0001 0 37 37
2912524 3057 3/16/2010 1/1/0001 0 37 37
2301389 3057 3/30/2010 1/1/0001 0 37 37
2876580 3057 4/2/2010 1/1/0001 0 37 37
5995004 3057 4/6/2010 1/1/0001 0 37 37
3399558 3057 4/20/2010 1/1/0001 0 37 37
3588772 3057 4/27/2010 1/1/0001 0 36 37
3398955 3057 5/5/2010 5/11/2010 5/6/2010 0 37 36
3572316 3057 5/6/2010 1/1/0001 0 37 37
4107691 3057 5/18/2010 1/1/0001 0 37 37
5225508 3057 6/1/2010 1/1/0001 0 37 37
4407607 3057 6/15/2010 1/1/0001 0 36 37
5117714 3057 6/18/2010 6/29/2010 7/6/2010 7 37 0 36
5440718 3057 7/6/2010 1/1/0001 0 36 37
5812207 3057 7/17/2010 7/30/2010 7/31/2010 1 37 0 36
6758285 3057 7/31/2010 1/1/0001 0 37 37