Archives of the TeradataForum

Message Posted: Tue, 09 Nov 2010 @ 20:39:37 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

You can use the order OLAP functions to do this easily. You can use the max() OVER to find the cumulative maximum date for any row within the member id preceding your current row by adjusting the sliding window (row between unbounded preceding and 1 preceding). Then either subtract the dates or compare the current row date minus 12 months to the max date marking a "Y" or "N" depending on the current row admittance date minus the cumulative maximum admittance date.

Here is an example to illustrate the point (you can improve it):

     create volatile table vt_admittance
        (memberid integer not null,date_Admitted date not null)
        on commit preserve rows;

     insert into vt_admittance values (311123,date'2009-01-08');
     insert into vt_admittance values (311123,date'2010-01-01');
     insert into vt_admittance values (489900,date'2008-08-09');
     insert into vt_admittance values (489900,date'2010-02-02');

     select memberid
          ,max(twice_in_12mo) as two_in_12_months from (select memberid
             ,add_months(date_admitted,-12) as threshold_date
             ,max(date_admitted) over (partition by memberid
                          order by memberid, date_admitted
                          rows between
                   unbounded preceding and 1 preceding) as last_admitted
             ,case when threshold_Date <= last_Admitted then 'Y' else 'N' end as twice_in_12mo
        from vt_admittance) a
     group by 1
     order by 1;

Kyle Prescott
DBA Manager
Unum-EHDS
Chattanooga, TN

	<Prev	Next>		<<First	<Prev	Next>	Last>>