Archives of the TeradataForum

Message Posted: Thu, 11 Aug 2005 @ 15:44:47 GMT


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

Subj:		Re: Help need on Tricky SQL

From:		Dieter Noeth

Anomy Anom wrote:

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

No duplicate dates per key?

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

     select
        keyval, min(applied), max(applied)
     from
       (
        select
          keyval, applied,
          applied - rank() over (partition by keyval order by applied) as x
        from anomy
       ) dt
     group by valkey, x
     order by 2

Dieter


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

Attachments

Library

Quick Reference