Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 11 Aug 2005 @ 09:42:02 GMT

  <Prev Next>  
Next> Last>>  

Subj:   Help need on Tricky SQL
From:   Anomy Anom

<-- Anonymously Posted: Thursday, August 11, 2005 02:00 -->

I have a table with a key value and date value - the dates are sequential eg

     Key          Applied
     A            2005-01-01
     A            2005-01-02
     A            2005-01-03
     B            2005-01-04
     B            2005-01-05
     A            2005-01-06
     A            2005-01-07
     C            2005-01-08

Now I want a view to summarise this so I get each key value and the range of dates it is applied to. So the result set I want to see is:

     Key      Start          End
     A        2005-01-01     2005-01-03
     B        2005-01-04     2005-01-05
     A        2005-01-06     2005-01-07
     C        2005-01-08     2005-01-08

If I make my view "SELECT key, MAX(applied), MIN(applied)" I don't get what I want as it doesn't take into account the 2 distinct occurrence of key "A", ie I get

     Key     Start          End
     A       2005-01-01     2005-01-07
     B       2005-01-04     2005-01-05
     C       2005-01-08     2005-01-08

I thought the analytical functions might help me, something like

select key ,MIN(applied) OVER (PARTITION BY Key ORDER BY applied), MAX(applied) OVER (PARTITION BY Key ORDER BY applied) but this doesn't do it, and I haven't been able to nut it out.

Any ideas?

  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020