Archives of the TeradataForum

Message Posted: Wed, 10 Nov 2010 @ 07:49:23 GMT

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

<-- Anonymously Posted: Wednesday, November 10, 2010 00:58 -->

For current scenario you can fire the below statement.

     SELECT  a.member_id ,
             CASE
                  WHEN  (  b.dt1 - b.dt2 ) < 365 THEN 'YES'
                  ELSE  'NO'
             END
     FROM    temp1 a ,
            ( sel member_id , MAX(date_admitted) dt1 , MIN(date_admitted) dt2
              FROM    temp1
              GROUP   BY 1
            )b
     WHERE   a.member_id = b.member_id
     GROUP   BY 1  ,2

In future with many records of identical member_id you can use below statement.

     sel a.member_id , a.date_admitted , b.date_admitted
       , COALESCE(MIN(b.date_admitted) over
      ( PARTITION BY b.member_id
      ORDER   BY b.date_admitted rows BETWEEN 1 following
             AND     1 following),b.date_admitted),
                     CASE
                           WHEN    (  MIN( b.date_admitted) over
      ( PARTITION BY b.member_id
     ORDER   BY b.date_admitted rows BETWEEN 1 following
             AND     1 following) - a.date_admitted ) < 365 THEN 'YES'
                             ELSE    'NO'
                     END
     FROM    temp1 a ,
     ( sel member_id , date_admitted
     FROM    temp1
     )b
     WHERE   a.member_id = b.member_id
             AND     a.date_admitted = b.date_admitted
     ORDER   BY 1,2

Regards

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