Skip to Main Content

Help needed in writing the SQL

GiohuiAug 2 2019 — edited Aug 2 2019

Hi ,

firstly glad to be here in this Oracle forum , amazed by the knowledge shared and helping from fellow community members.

I need help on building SQL logic from below

Data to be present in the table :

Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'TAX',21932.9712,16,'2');

Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'TAX',0,0,'1');

Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'VCT',0,0,null);

Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'REASON',0,0,null);

Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'UNR',37317.36,0,'2');

Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'REASON',-105.1152,16,null);

Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'UNR',12297.6,0,'1');

Table looks like below

pastedImage_1.png

SQL/PLSQL logic to build from below logic

a) Row number (1,5) and row number (2,7) are related as they have same LINE_NUMBER_TYPE values

b) Row number 1,6 are related with VAT_RATE value , but here 1, 5 already related with LINE_NUMBER_TYPE value ,so here 6,5 rows are indirecly related

c) I want to calculate (row_no_5.amount-row_no_6.amount) * row_no_6.vat_rate , after the calculation I need to update result back to row_no_1.amount value

Appreciate your help on this

Thank you

Comments
Post Details
Added on Aug 2 2019
10 comments
183 views