Skip to Main Content

Formatting and Summary rollup

AQHAug 31 2019 — edited Sep 27 2019

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)

pastedImage_0.png

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'));

Comments
Post Details
Added on Aug 31 2019
12 comments
349 views