Preceding versus Following using TOAD in Teradata

Toad Data Point
1

Hi, I recently got help figuring out how to use the"Preceding" function which worked well, Hurray!

The intent was to create a value called Day Diff subtracting a precedingdischarge date with a subsequent admit date and flag the following admit/discharge with a1 if the following admit date was within 30 days of the preceding discharge, else 0. That field iscalled “30DyReAdFLG”

I now need to reverse the process that I developed and use the"Following" function.

I still need to create the Day Diff value but using the “Following” function,and flag the preceding discharge date when the following admit date is within30 days, else 0.

It may seem like the same thing but actually is not, it has to do with which record gets flagged, the preceeding admit/discharge or the following admit/discharge record.

I have it almost working with the exception that when I am creating the valuecalled “30DyReAdFLG” which is the difference between a preceeding discharge dateand a subsequent admit date in days, and although it is populating correctly, it ispopulating with a negative(-) number.

The negative number causes all records to be flagged as 1

I have tried a few different permutationsbut cannot get rid of the negatives. Maybe someone with more experience canspot the problem.

Here is my “Preceding” script, and my “Following” script as well as some current output for the following script illustrating the (-)'s for better understanding. Any support would be appreciated, thank you.

CREATE TABLEar_asrh_dm_etl_beacon.Flg_ReAdAlgo1a4 AS

(SELECT AdmitClaim_id2,AdmitMem_id2, ADte AS admit, DDte AS discharge,

MAX(DDte) over (partition BY AdmitMem_id2 ORDER BY AdmitMem_id2,ADte

ROWS between 1 preceding and 1 preceding) AS “Prev. Disch.”,

ADte - MAX(DDte) over (partition by AdmitMem_id2 ORDER BY AdmitMem_id2,ADte

ROWS between 1 preceding and 1 preceding ) AS “Day Diff”,

CASE WHEN

ADte - MAX(DDte) over (partition BY AdmitMem_id2 ORDER BY AdmitMem_id2,ADte

ROWS between 1 preceding and 1 preceding ) <= 30

THEN 1 ELSE 0 END AS"30DyReAdFLG", AdmitPatStat_dl2, AdmitPatStat_id2,

     AdmitProvRend_id2,AdmitProvRend_LastNamed2

FROM ar_asrh_dm_etl_beacon.Flg_ReAdAlgo1a3

GROUP BY AdmitMem_id2, ADte, AdmitClaim_id2, DDte, AdmitPatStat_dl2,AdmitPatStat_id2, AdmitProvRend_id2,AdmitProvRend_LastNamed2)

WITH DATA;

CREATE TABLEar_asrh_dm_etl_beacon.Flg_ReAdAlgo1a5 AS

(SELECT AdmitClaim_id2,AdmitMem_id2, ADte AS admit, DDte AS discharge,

MAX(ADte) over (partition BY AdmitMem_id2 ORDER BY AdmitMem_id2,DDte

ROWS between 1 following and 1 following) AS “SubsequentAdmit”,

DDte - MAX(ADte) over (partition by AdmitMem_id2 ORDER BY AdmitMem_id2,DDte

ROWS between 1 following and 1 following ) AS “Day Diff”,

CASE WHEN

DDte - MAX(ADte) over (partition BY AdmitMem_id2 ORDER BY AdmitMem_id2,DDte

ROWS between 1 following and 1 following ) <= 30

THEN 1 ELSE 0 END AS"30DyReAdFLG", AdmitPatStat_dl2, AdmitPatStat_id2,

AdmitProvRend_id2,AdmitProvRend_LastNamed2

FROM ar_asrh_dm_etl_beacon.Flg_ReAdAlgo1a3

GROUP BY AdmitMem_id2, ADte, AdmitClaim_id2, DDte, AdmitPatStat_dl2,AdmitPatStat_id2, AdmitProvRend_id2, AdmitProvRend_LastNamed2)

WITH DATA;

I used the wrong example so this is edited now.

AdmitClaim_id2 AdmitMem_id2 admit discharge SubsequentAdmit Day Diff 30DyReAdFLG
1005821 3057 1/24/2010 2/4/2010 2/25/2010 -21 1
2304418 3057 2/25/2010 3/5/2010 5/5/2010 -61 1
3398955 3057 5/5/2010 5/11/2010 6/18/2010 -38 1
5117714 3057 6/18/2010 6/29/2010 7/17/2010 -18 1
5812207 3057 7/17/2010 7/30/2010 8/18/2010 -19 1
6092480 3057 8/18/2010 8/24/2010 9/4/2010 -11 1
7161403 3057 9/4/2010 9/22/2010 9/24/2010 -2 1
7162225 3057 9/24/2010 9/30/2010 10/13/2010 -13 1
7450820 3057 10/13/2010 10/14/2010 10/15/2010 -1 1
7503170 3057 10/15/2010 10/16/2010 0

Message was edited by: wpoling_953

2

This is the same problem as last time, except that the query should be “looking ahead to the next row” instead of “looking back to the previous row”.

Instead of the lag function, you would use the lead function.

Since Teradata doesn’t have LAG, I imagine it doesn’t have LEAD.

Instead, you would take the other solution given, the solution that returned the MAX (DISCHARGE_DATE) of the previous row, and modify it to return the MIN (ADMIT_DATE) of the following row.

drop table large ;
create table large
(uniq1 number, mid number, admit_date date, discharge_date date,
prdi varchar2 (16)) ;
insert into large (uniq1, mid, admit_date, discharge_date, prdi)
values (123, 1, DATE ‘2010-01-24’, DATE ‘2010-02-04’, ‘PRDI-SOMETHING’) ;
insert into large (uniq1, mid, admit_date, discharge_date, prdi)
values (321, 1, DATE ‘2010-02-25’, DATE ‘2010-03-05’, ‘PRDI-OTHER’) ;
insert into large (uniq1, mid, admit_date, discharge_date, prdi)
values (789, 1, DATE ‘2010-06-21’, DATE ‘2012-06-28’, ‘PRDI-DIFFERENT’) ;
insert into large (uniq1, mid, admit_date, discharge_date, prdi)
values (1011, 2, DATE ‘2012-05-15’, DATE ‘2012-05-16’, ‘PRDI-UNUSUAL’) ;
insert into large (uniq1, mid, admit_date, discharge_date, prdi)
values (1291, 2, DATE ‘2012-09-25’, DATE ‘2012-10-05’, ‘PRDI-OFFBEAT’) ;
insert into large (uniq1, mid, admit_date, discharge_date, prdi)
values (1374, 2, DATE ‘2012-10-30’, DATE ‘2012-11-02’, ‘PRDI-DISTINCTIVE’) ;
commit ;

Query using LEAD
select uniq1, mid, admit_date as admit, discharge_date as discharge,
lead (admit_date, 1, null)
over (partition by mid order by mid, admit_date) as “Next Admit.”,
lead (admit_date, 1, null)
over (partition by mid order by mid, admit_date) - discharge_date as “Day Diff”,
case
when
lead (admit_date, 1, null)
over (partition by mid order by mid, admit_date) - discharge_date <= 30
then 1 else 0
end as “30DyReAdFLG”,
prdi
from large
order by mid, admit_date ;

Equivalent query using MIN of the FOLLOWING row
select uniq1, mid, admit_date as admit,
discharge_date as discharge,
min (admit_date) over (partition by mid order by mid, admit_date
rows between 1 following and 1 following)
as “Next Admit”,
min (admit_date) over (partition by mid order by mid, admit_date
rows between 1 following and 1 following )
- discharge_date as “Day Diff”,
case
when min (admit_date) over (partition by mid order by mid, admit_date
rows between 1 following and 1 following )
- discharge_date <= 30
then 1
else 0
end as “30DyReAdFLG”,
prdi
from large
order by mid, admit_date ;

Output

UNIQ1 MID ADMIT DISCHARGE Next Admi Day Diff 30DyReAdFLG PRDI

123 1 24-JAN-10 04-FEB-10 25-FEB-10 21 1 PRDI-SOMETHING
321 1 25-FEB-10 05-MAR-10 21-JUN-10 108 0 PRDI-OTHER
789 1 21-JUN-10 28-JUN-12 0 PRDI-DIFFERENT
1011 2 15-MAY-12 16-MAY-12 25-SEP-12 132 0 PRDI-UNUSUAL
1291 2 25-SEP-12 05-OCT-12 30-OCT-12 25 1 PRDI-OFFBEAT
1374 2 30-OCT-12 02-NOV-12 0 PRDI-DISTINCTIVE

3

Good morning Jacques this looks like it will do it. I will retool your example with my objects and try it, then I will close this question when completed, thanks so much, really appreciate your support!

4

Yep, that’s got it for now, thanks Jacques, I will close this question as answered.

5

I’m glad that worked. Using these analytic functions like MIN (…) OVER (PARTITION BY … ORDER BY …) can solve a lot of problems. It takes a while to understand them but once you do it makes writing many queries much easier.