Archives of the TeradataForum

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


	<Prev	Next>		<<First	<Prev	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>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference