I have a table in which I got data for accounts for each day. Most of the times on a given day there will be two rows one with code 9 and other with code 0.
I want to get total data subtract col2 value (9) - col2 value (0). To explain further here is sample data.
I want to get the data by COL1 (Account) and by COLDATE. Right now it is grouping all accounts and not considering the date.
create table SAMPLETABLE
(
COL1 NUMBER(10) not null,
COLDATE DATE not null,
COL2 NUMBER(15, 9) not null,
COLCODE NUMBER(1)
) ;
insert into SAMPLETABLE (COL1, COLDATE, COL2, COLCODE)
values ('9889898989', to_date('01-01-2014', 'dd-mm-yyyy'), 68.6370000000, 0);
insert into SAMPLETABLE (COL1, COLDATE, COL2, COLCODE)
values ('9889898989', to_date('02-01-2014', 'dd-mm-yyyy'), 53.4620000000, 0);
insert into SAMPLETABLE (COL1, COLDATE, COL2, COLCODE)
values ('9889898989', to_date('01-01-2014', 'dd-mm-yyyy'), 80.5020000000, 9);
insert into SAMPLETABLE (COL1, COLDATE, COL2, COLCODE)
values ('9889898989', to_date('02-01-2014', 'dd-mm-yyyy'), 110.5940000000, 9);
insert into SAMPLETABLE (COL1, COLDATE, COL2, COLCODE)
values ('10101010', to_date('01-01-2014', 'dd-mm-yyyy'), 999, 9);
Present
| TOTAL | COL1 | COLDATE | COL2 | COLCODE |
| 9889898989 | 1/1/2014 | | 80.502000000 | 9 |
| -30.092 | 9889898989 | 1/2/2014 | ########### | 9 |
| -888.406 | 10101010 | 1/1/2014 | ########### | 9 |
| 930.363 | 9889898989 | 1/1/2014 | 68.637000000 | 0 |
| 15.175 | 9889898989 | 1/2/2014 | 53.462000000 | 0 |
Expected
| TOTAL | COL1 | COLDATE | COL2 |
| 999.000000000 | 10101010 | 1/1/2014 | 999.000000000 |
| 11.865000000 | 9889898989 | 1/1/2014 | 80.502000000 |
| 57.132 | 9889898989 | 1/2/2014 | 110.594000000 |
select
(lag(c.COL2) over (order by c.COLCODE desc) - c.COL2) as total,
c.COL1
,
c.COLDATE,
c.COL2,
c.COLCODE
from SAMPLETABLE c
where c.COLDATE BETWEEN '01-JAN-2014' AND '02-JAN-2014'