Archives of the TeradataForum

Message Posted: Wed, 15 Nov 2006 @ 10:11:45 GMT

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

RAJESH.TAMBE wrote:

This is an example i use in my trainings:

     CREATE TABLE seqtest(seqval INT NOT NULL PRIMARY KEY)
     ;

     INSERT INTO seqtest VALUES ( 3)
     ;INSERT INTO seqtest VALUES ( 4)
     ;INSERT INTO seqtest VALUES ( 5)
     ;INSERT INTO seqtest VALUES ( 8)
     ;INSERT INTO seqtest VALUES ( 9)
     ;INSERT INTO seqtest VALUES (11)
     ;INSERT INTO seqtest VALUES (18)
     ;INSERT INTO seqtest VALUES (19)
     ;INSERT INTO seqtest VALUES (20)
     ;


     /* missing values:
       [1..2]
        6..7
       10
       12..17
     */

     SELECT
       seqval - #IDs AS GapStart,
       seqval - 1 AS GapEnd,
       (COALESCE(seqval
                 - MIN(seqval) OVER
                      (order by seqval
                       ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
                 ,seqval)) - 1 AS #IDs
     FROM
       seqtest
     QUALIFY
       #IDs > 0;


     /* used values:
       3..5
       8..9
      11
      18..20
     */


     SELECT
       MIN(seqval) AS StartVal,
       MAX(seqval) AS EndVal,
       COUNT(*)
     FROM
      (
       SELECT
         seqval - RANK() OVER (order by seqval ASC) AS grp,
         seqval
       FROM seqtest
      ) dt
     GROUP BY grp
     order by 1
     ;

Dieter

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