Sunday, April 28, 2013

Oracle:How to get first and last rows in an aggregation

create table transactions (
id integer not null,
tdate date not null,
opening_bal number(8,2) not null,
closing_bal number(8,2) not null,
transaction number(8,2) not null,
constraint pk_transactions primary key (id,tdate));

insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'01-jan-10',0,10,10);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'02-jan-10',10,20,10);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'03-jan-10',20,25,5);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'04-jan-10',25,-15,-40);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'05-jan-10',-15,0,15);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'06-jan-10',0,13,13);


SQL> select * from transactions;

        ID TDATE     OPENING_BAL CLOSING_BAL TRANSACTION
---------- --------- ----------- ----------- -----------
         1 01-JAN-10           0          10          10
         1 02-JAN-10          10          20          10
         1 03-JAN-10          20          25           5
         1 04-JAN-10          25         -15         -40
         1 05-JAN-10         -15           0          15
         1 06-JAN-10           0          13          13

6 rows selected.
SQL> select id, count(tdate) as tcount
  ,max(opening_bal) keep (dense_rank first order by tdate) as opening_bal
  ,max(closing_bal) keep (dense_rank last order by tdate) as closing_bal 
from transactions 
where tdate between '02-jan-10' and '04-jan-10' 
group by id;

        ID TCOUNT       OPENING_BAL CLOSING_BAL
---------- ------------ ----------- -----------
         1            3          10         -15

SQL>

0 comments:

Post a Comment

Please Post your Comments..!