i have a table ml with following script; and i require following result-set ; it is that if where date is DT > TO_DATE('07/31/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS') then result set with o/b (opening balance) auto populate sum of dr/cr with DT <= TO_DATE('07/31/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS') for each ACID, can any one assist
| table | data |
|
ml table
CREATE TABLE ML
(
ID NUMBER,
DT DATE,
DR NUMBER,
CR NUMBER,
REMARKS VARCHAR2(10 BYTE),
ACID NUMBER,
JRNL NUMBER
)
|
INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)
VALUES (1,to_date('31-JUL-2018', 'DD-MON-YYYY'),0,541698,'cb',473,16);
INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)
VALUES (2,to_date('31-JUL-2018','DD-MON-YYYY') ,541698,0,'cb',341,16);
INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)
VALUES (3,TO_DATE('25-AUG-2018','DD-MON-YYYY'),197550,0,'tr',473,22);
INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)
VALUES (4,TO_DATE('25-AUG-2018','DD-MON-YYYY'),0,8890,'tr',468,22);
INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)
VALUES (5,TO_DATE('25-AUG-2018','DD-MON-YYYY'),0,188660,'tr',30,22);
INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)
VALUES (6,TO_DATE('31-AUG-2018','DD-MON-YYYY'),6100,0,'tr',341,2);
INSERT INTO ML (ID,DT,CR, DR,REMARKS,ACID,JRNL)
VALUES (7,TO_DATE('31-AUG-2018','DD-MON-YYYY'),0,6100,'tr',473,2);
|
Message was edited by: AQH