Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 16 Jun 2003 @ 18:08:36 GMT


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


Subj:   Re: Consecutive sequence sql logic
 
From:   Dwight Etheridge

I took the Dubery logic a little further. To really see this in action, repeat the qualify conditions in the select projection. I like mdiff() better, but the V2R5 manual now says "The use of MDIFF is strongly discouraged", so a I switched it to the verbose form.

create  table test_me
(acct integer
,phone char(8) );
ins test_me(121212, '555-1211' );
ins test_me(121212, '555-1212' );
ins test_me(121212, '555-1213' );
ins test_me(121212, '555-1214');
ins test_me(121212, '555-1215' );
ins test_me(121212, '555-1216' );
ins test_me(921212, '555-1111' );
ins test_me(921212, '555-4444');

sel distinct acct
    from test_me
qualify
phone - sum(phone) over (order by acct,phone rows between 1 preceding
and 1 preceding)=1
and phone - sum(phone) over (order by acct,phone rows between 2
preceding and 2 preceding)=2
and phone - sum(phone) over (order by acct,phone rows between 3
preceding and 3 preceding)=3
and phone - sum(phone) over (order by acct,phone rows between 4
preceding and 4 preceding)=4;

--
Dwight Etheridge
Teradata Certified Master



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016