respected all. i need following set from below tables/data. i get this from sample data with date condition for trdate from '01-Jul-2016' to 30-Jun-2017' and IIAD in (44). following set shows Group/ hierarchy values in Bold font. ALEVEL table has accountid, with parentleft,parentright and parentid (as i get it from existing database) and doesnt' no how the logic is working to get the following set. please assist. (im using oracle 11g express)
| data |
CREATE TABLE ALEVEL
(
ID NUMBER(5),
NAME VARCHAR2(100 BYTE),
PARENTLEFT NUMBER(5),
PARENTRIGHT NUMBER(5),
PARENTID NUMBER(5),
TYPE VARCHAR2(6 BYTE)
);
ALTER TABLE ALEVEL ADD CONSTRAINT ALEVEL_PK PRIMARY KEY (ID);
CREATE TABLE AML
(
ID NUMBER,
DEBIT NUMBER,
ACCOUNT_ID NUMBER,
CREDIT NUMBER,
TRDATE DATE,
AAID NUMBER
)
;
ALTER TABLE AML ADD CONSTRAINT AML_PK
PRIMARY KEY
(ID);
INSERT INTO ALEVEL ( ID, NAME,PARENTLEFT,PARENTRIGHT,PARENTID, TYPE)
VALUES (204,'A1',74,87,'','group');
INSERT INTO ALEVEL ( ID, NAME,PARENTLEFT,PARENTRIGHT,PARENTID, TYPE)
VALUES (205,'A101',75,86,204,'group');
INSERT INTO ALEVEL ( ID, NAME,PARENTLEFT,PARENTRIGHT,PARENTID, TYPE)
VALUES (489,'A10105',80,81,205,'detail');
INSERT INTO ALEVEL ( ID, NAME,PARENTLEFT,PARENTRIGHT,PARENTID, TYPE)
VALUES (479,'B2',444,485,'','group');
INSERT INTO ALEVEL ( ID, NAME,PARENTLEFT,PARENTRIGHT,PARENTID, TYPE)
VALUES (484,'B202',457,478,479,'group');
INSERT INTO ALEVEL ( ID, NAME,PARENTLEFT,PARENTRIGHT,PARENTID, TYPE)
VALUES (505,'B20202',468,469,484,'detail');
INSERT INTO ALEVEL ( ID, NAME,PARENTLEFT,PARENTRIGHT,PARENTID, TYPE)
VALUES (499,'B20209',470,473,484,'group');
INSERT INTO ALEVEL ( ID, NAME,PARENTLEFT,PARENTRIGHT,PARENTID, TYPE)
VALUES (500,'B2020901',471,472,499,'detail');
INSERT INTO ALEVEL ( ID, NAME,PARENTLEFT,PARENTRIGHT,PARENTID, TYPE)
VALUES (490,'C3',486,511,'','group');
INSERT INTO ALEVEL ( ID, NAME,PARENTLEFT,PARENTRIGHT,PARENTID, TYPE)
VALUES (491,'C301',487,494,490,'detail');
INSERT INTO ALEVEL ( ID, NAME,PARENTLEFT,PARENTRIGHT,PARENTID, TYPE)
VALUES (497,'C302',495,498,490,'detail');
INSERT INTO ALEVEL ( ID, NAME,PARENTLEFT,PARENTRIGHT,PARENTID, TYPE)
VALUES (506,'D4',530,533,'','group');
INSERT INTO ALEVEL ( ID, NAME,PARENTLEFT,PARENTRIGHT,PARENTID, TYPE)
VALUES (507,'D401',531,532,506,'detail');
INSERT INTO AML (ID, ACCOUNT_ID, AAID,DEBIT,CREDIT, TRDATE)
VALUES (154,491,44,19000,0,to_date('17-03-2016','dd-mm-yyyy'));
INSERT INTO AML (ID, ACCOUNT_ID, AAID,DEBIT,CREDIT, TRDATE)
VALUES (153,505,44,0,19000,to_date('17-03-2016','dd-mm-yyyy'));
INSERT INTO AML (ID, ACCOUNT_ID, AAID,DEBIT,CREDIT, TRDATE)
VALUES (157,500,44,0,3113,to_date('13-04-2016','dd-mm-yyyy'));
INSERT INTO AML (ID, ACCOUNT_ID, AAID,DEBIT,CREDIT, TRDATE)
VALUES (155,505,44,69180,0,to_date('13-04-2016','dd-mm-yyyy'));
INSERT INTO AML (ID, ACCOUNT_ID, AAID,DEBIT,CREDIT, TRDATE)
VALUES (156,497,44,0,66067,to_date('13-04-2016','dd-mm-yyyy'));
INSERT INTO AML (ID, ACCOUNT_ID, AAID,DEBIT,CREDIT, TRDATE)
VALUES (158,505,44,0,500,to_date('06-09-2016','dd-mm-yyyy'));
INSERT INTO AML (ID, ACCOUNT_ID, AAID,DEBIT,CREDIT, TRDATE)
VALUES (159,491,44,500,0,to_date('06-09-2016','dd-mm-yyyy'));
INSERT INTO AML (ID, ACCOUNT_ID, AAID,DEBIT,CREDIT, TRDATE)
VALUES (149,497,44,10000000,0,to_date('07-09-2016','dd-mm-yyyy'));
INSERT INTO AML (ID, ACCOUNT_ID, AAID,DEBIT,CREDIT, TRDATE)
VALUES (150,489,44,0,10000000,to_date('07-09-2016','dd-mm-yyyy'));
INSERT INTO AML (ID, ACCOUNT_ID, AAID,DEBIT,CREDIT, TRDATE)
VALUES (161,491,44,500,0,to_date('08-09-2016','dd-mm-yyyy'));
INSERT INTO AML (ID, ACCOUNT_ID, AAID,DEBIT,CREDIT, TRDATE)
VALUES (160,505,44,0,500,to_date('08-09-2016','dd-mm-yyyy'));