Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 14 May 2008 @ 19:11:00 GMT


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


Subj:   Re: Date sequence with RANK() OVER
 
From:   Mohommod.Khan

Schabek,

Some thing like this, should get you what you want. The output should look as follows:


Thanks!


     PARENT_ID ---Data1-----Data2-------        Data3--Data4----Data5----
         Data6-----TCount
     100        05|2004-01-01        04|2003-12-01        03|2003-11-01
       02|2002-12-01        01|2002-11-01                  5
     200        03|2003-11-01        02|2002-12-01        01|2002-11-01
                                                                     3


     sel
     parent_ID
     ,Max(
            Case
               When rnk = 1
                  Then prod_ID|| '|' || INSTALLED_DATE
                  Else '  '
                  end) As Data1
     ,Max(
            Case
               When rnk = 2
                  Then prod_ID  || '|' ||  INSTALLED_DATE
                  Else '  '
                  end) As Data2
     ,Max(
            Case
               When rnk = 3
                  Then prod_ID || '|' ||  INSTALLED_DATE
                  Else '  '
                  end) As Data3
     ,Max(
            Case
               When rnk =4
                  Then prod_ID  || '|' ||  INSTALLED_DATE
                  Else '  '
                  end) As Data4
     ,Max(
            Case
               When rnk = 5
                  Then prod_ID  || '|' ||   INSTALLED_DATE
                  Else '  '
                  end) As Data5
     ,Max(
            Case
               When rnk = 6
                  Then prod_ID  || '|' ||   INSTALLED_DATE
                  Else '  '
                  end) As Data6
       , COUNT(PROD_ID) AS TCount

     From
     (
     sel
     PARENT_ID
     ,PROD_ID
     ,INSTALLED_DATE  (char(10))
     ,rank(INSTALLED_DATE  desc) AS RNK
     From
     example
     GROUP BY  1
     ) dt
     Group By 1
     Order By 1,2
     ;


     
  <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