Using Oracle's built-in Lead and Lag functions, it is easy to create reports
which display next / previous values even when the primary key is not consecutive
(i.e. dates, week days).
set pagesize 110 ;
create table regional_sales (
sales_id integer,
sales_month_dt date,
total_sales number
);
insert into regional_sales values (101, '1-jan-04', 1000) ;
insert into regional_sales values (102, '1-feb-04', 2000) ;
insert into regional_sales values (103, '1-mar-04', 3000) ;
insert into regional_sales values (104, '1-apr-04', 4000) ;
insert into regional_sales values (105, '1-may-04', 5000) ;
insert into regional_sales values (106, '1-jun-04', 6000) ;
insert into regional_sales values (107, '1-jul-04', 7000) ;
insert into regional_sales values (108, '1-aug-04', 8000) ;
insert into regional_sales values (110, '1-sep-04', 9000) ;
insert into regional_sales values (112, '1-oct-04', 10000) ;
insert into regional_sales values (114, '1-nov-04', 11000) ;
insert into regional_sales values (119, '1-dec-04', 12000) ;
commit ;
select sales_id, lead (total_sales) over (order by sales_id) as lead_val
from regional_sales ;
SALES_ID LEAD_VAL
---------- ----------
101 2000
102 3000
103 4000
104 5000
105 6000
106 7000
107 8000
108 9000
110 10000
112 11000
114 12000
119
|
|
