Archives of the TeradataForum

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

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

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>>