Archives of the TeradataForum

Message Posted: Thu, 11 Aug 2005 @ 11:19:10 GMT

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

Try this SQL

     SELECT A.PKEY, A.Applied, COALESCE (B.applied ,( '9999-12-31' (Date, format 'YYYY-MM-DD')))
     FROM
     (
     SELECT PKey PKEY, applied applied,RANK () OVER  (PARTITION BY PKEY
     ORDER BY   applied ASC ) ORD
     FROM SQL1) A LEFT OUTER JOIN (
     SELECT PKey PKEY, applied applied,RANK () OVER  (PARTITION BY PKEY
     ORDER BY   applied ASC ) ORD
     FROM SQL1)B
     ON  A.PKEY = B.PKEY
     AND A.ORD = B.ORD-1
     order by 1,2,3

But this Joins on Derived values and Performance may not be good.

Thanks.

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