Thanks in Advance
I have Table like below
ID TYPE NUM NAME BEG_MP END_MP VALUE
10001103N 3 1190001 WST 0.000 0.220
10001103N 3 1190002 WST 0.220 0.440
10001103N 3 1190003 WST 0.440 0.820 12800
10001103N 3 1190003 WST 0.820 1.180 12800
10001103N 3 1190004 WST 1.180 1.220
10001103N 3 1190004 WST 1.220 1.300
10001103N 3 1190005 WST 1.300 1.420 14800
10001103N 3 1190005 WST 1.420 1.550 14800
10001103N 3 1190006 WST 1.550 2.030
10001103N 3 1190006 WST 2.030 2.660
10001103N 3 1190007 WST 2.660 2.780
What i need is to fill gaps with Lead and Lag value and do Average and Gap in between values
ID TYPE NUM NAME BEG_MP END_MP VALUE
10001103N 3 1190001 WST 0.000 0.220 12800 ---> Lag value
10001103N 3 1190002 WST 0.220 0.440 12800 ---> Lag Value
10001103N 3 1190003 WST 0.440 0.820 12800
10001103N 3 1190003 WST 0.820 1.180 12800
10001103N 3 1190004 WST 1.180 1.220 13800 ---> Avg(12800,14800)
10001103N 3 1190004 WST 1.220 1.300 13800 ---> Avg(12800,14800)
10001103N 3 1190005 WST 1.300 1.420 14800
10001103N 3 1190005 WST 1.420 1.550 14800
10001103N 3 1190006 WST 1.550 2.030 14800 ---> Lead Value
10001103N 3 1190006 WST 2.030 2.660 14800 ---> Lead Value
10001103N 3 1190007 WST 2.660 2.780 14800 ---> Lead Value
create table AVG_TABLE
(
ID VARCHAR2(20),
TYPE NUMBER,
NUM NUMBER,
NAME VARCHAR2(10),
VALUE NUMBER,
BEG_MP NUMBER(6,3),
END_MP NUMBER(6,3)
)
;
insert into AVG_TABLE (ID, TYPE, NUM, NAME, VALUE, BEG_MP, END_MP)
values ('10001103N', 3, 1190001, 'WST', null, 0, .22);
insert into AVG_TABLE (ID, TYPE, NUM, NAME, VALUE, BEG_MP, END_MP)
values ('10001103N', 3, 1190002, 'WST', null, .22, .44);
insert into AVG_TABLE (ID, TYPE, NUM, NAME, VALUE, BEG_MP, END_MP)
values ('10001103N', 3, 1190003, 'WST', 12800, .44, .82);
insert into AVG_TABLE (ID, TYPE, NUM, NAME, VALUE, BEG_MP, END_MP)
values ('10001103N', 3, 1190003, 'WST', 12800, .82, 1.18);
insert into AVG_TABLE (ID, TYPE, NUM, NAME, VALUE, BEG_MP, END_MP)
values ('10001103N', 3, 1190004, 'WST', null, 1.18, 1.22);
insert into AVG_TABLE (ID, TYPE, NUM, NAME, VALUE, BEG_MP, END_MP)
values ('10001103N', 3, 1190004, 'WST', null, 1.22, 1.3);
insert into AVG_TABLE (ID, TYPE, NUM, NAME, VALUE, BEG_MP, END_MP)
values ('10001103N', 3, 1190005, 'WST', 14800, 1.3, 1.42);
insert into AVG_TABLE (ID, TYPE, NUM, NAME, VALUE, BEG_MP, END_MP)
values ('10001103N', 3, 1190005, 'WST', 14800, 1.42, 1.55);
insert into AVG_TABLE (ID, TYPE, NUM, NAME, VALUE, BEG_MP, END_MP)
values ('10001103N', 3, 1190006, 'WST', null, 1.55, 2.03);
insert into AVG_TABLE (ID, TYPE, NUM, NAME, VALUE, BEG_MP, END_MP)
values ('10001103N', 3, 1190006, 'WST', null, 2.03, 2.66);
insert into AVG_TABLE (ID, TYPE, NUM, NAME, VALUE, BEG_MP, END_MP)
values ('10001103N', 3, 1190007, 'WST', null, 2.66, 2.78);
commit;