Help create a complex summary table for an accountant

[Xabinav]

Hello friends, I need your help to create a table in a special structure for accounting.

I have 2 tables as in the example:

Deal:

DealID	Date	Revenue
100	01/01/2020	1,000

Invoice:

Inv_ID	Name	DealID	Inv_Date	Inv_Amount
1	INV1	100	02/01/2020	200
2	INV2	100	01/02/2020	500
3	INV3	100	01/03/2020	300

************************************************************************************

Expected Form:

I'm trying to get to the table in this structure from 2 tables above...

ID	Name	DealID	Inv_Amount	Uninvoiced_Amount	Inv_Date
S-0	S0	100	0	1,000	01/01/2020
1	INV1	100	200	0	02/01/2020
S-1	S1	100	0	-1,000	02/01/2020
S-2	S2	100	0	800	02/01/2020
2	INV2	100	500	0	01/02/2020
S-3	S3	100	0	-800	01/02/2020
S-4	S4	100	0	300	01/02/2020
3	INV3	100	300	0	01/03/2020
S-5	S5	100	0	-300	01/03/2020
S-6	S6	100	0	0	01/03/2020

I would greatly appreciate your help, thank you

[Xabinav]

...

[ArnadoSandoval]

Hi @Xabinav 

You have to elaborate more about your expected result; the tables Deal and Invoice are associated by the DealID; the fields from these two tables does not contains the fields: ID and Uninvoiced_Amount, also the result table several records you do not explain where do they come from, you provided not enough information to assist you.

hope this helps

[Ksrinivasan]

hi,

i have try my level best, i have created in horizontal

ksrinivasan.