Archives of the TeradataForum

Message Posted: Tue, 14 Nov 2006 @ 20:15:47 GMT

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

Rajesh,

Here's one that can help and may need some changes.

     create table test ( Sno int);
     insert into test values (1001);
     insert into test values (1002);
     insert into test values (1003);
     insert into test values (1004);
     insert into test values (1005);
     insert into test values (1006);
     insert into test values (1007);
     insert into test values (1008);
     insert into test values (1009);
     insert into test values (1010);
     insert into test values (1011);
     insert into test values (1012);
     insert into test values (1015);
     insert into test values (1016);
     insert into test values (1017);
     insert into test values (1018);
     insert into test values (1019);
     insert into test values (1020);
     insert into test values (1023);
     insert into test values (1024);
     insert into test values (1025);
     insert into test values (1026);
     insert into test values (1050);
     insert into test values (1057);
     insert into test values (1080);
     insert into test values (1082);




     SEL
     Sno
     ,MIN(Sno) OVER(ORDER BY Sno) AS MIN_Seq
     ,SNO - SUM(Sno) OVER(ORDER BY Sno ROWS BETWEEN 1 PRECEDING AND 1
     PRECEDING) AS Mov_Seq
     , CASE WHEN Mov_Seq <> 1 AND Mov_Seq IS NOT NULL THEN 'Missing ' ||
     CAST((SNO + 1 - Mov_Seq) AS VARCHAR(100)) || ' Thru ' || CAST((SNO - 1)
     AS VARCHAR(100))
     WHEN Mov_Seq IS NULL THEN MIN_Seq
     WHEN Mov_Seq = 1 THEN Sno END
     FROM TEST T1 ;

Thanks,

Vinay Bagare

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