Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Compare data in same column
 
From:   Anomy Anom

<-- 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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023