Archives of the TeradataForum

Message Posted: Sun, 17 Oct 2004 @ 12:03:52 GMT


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

Subj:		Re: Windowing functions in TD

From:		Dieter Noeth

Sampath Shiva wrote:

I have a requirement where my Input/Source is as following

          Prty_id   Own_cd    D1      D2
          111       oc1        1       3
          111       oc1        4       7
          111       oc1        8      10
          111       oc2       11      20
          111       oc1       21      50
          111       oc1       51      60
          111       oc2       61      70
          111       oc2       71      80

And my output should be as follows :

          Prty_id   Own_cd    D1      D2
          111       oc1        1      10
          111       oc2       11      20
          111       oc1       21      60
          111       oc2       61      80

To explain it :

If Own_cd changes consecutively then min(D1) and max(D2) should be considered but if it is not then the record should be left as it is.

I have tried using windowing functions in TD like Min and Max... but did not had luck.

     create table Sampath(
        Prty_id int
        ,Own_cd char(3)
        ,D1     int
        ,D2     int
     ) primary index(Prty_id, own_cd)
     ;
     ins sampath(111,           'oc1',           1 ,     3);
     ins sampath(111,           'oc1',           4 ,     7);
     ins sampath(111,           'oc1',           8 ,     10);
     ins sampath(111,           'oc2',           11,     20);
     ins sampath(111,           'oc1',           21,     50);
     ins sampath(111,           'oc1',           51,     60);
     ins sampath(111,           'oc2',           61,     70);
     ins sampath(111,           'oc2',           71,     80);

     select
        prty_id,
        min(own_cd),
        min(d1),
        max(d2)
     from
       (
        select
          /*** calculate a group number of a row ***/
          sum(x) over (partition by prty_id
                       order by d1
                       rows unbounded preceding) as grp
          ,dt.*
        from
         (
          select
            /*** If own_cd changes then return 1 ***/
            case when min(own_cd) over (partition by prty_id
                          order by d1 asc
                          rows between 1 preceding and 1 preceding) = own_cd
            then 0 else 1 end as x
            ,s.*
          from sampath s
         ) dt
       ) dt
     group by prty_id, grp;

Another solution for the outer part:

     select
        prty_id,
        own_cd,
        min(d1) over (partition by prty_id, grp),
        max(d2) over (partition by prty_id, grp)
     from
     (
     ...
     ) dt
     qualify
        x = 1

Dieter


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

Attachments

Library

Quick Reference