I would like to get the following output from the table below: For each customer, the refunded invoices will be removed from the purchased invoices. For example, if a single product is purchased and the same product is refunded, both rows (invoices) will be deleted. However, if 2 products are purchased in a single invoice and one of them is returned, only the refunded invoice will be removed from the table by deducting the refund amount and income (refund invoice) from the invoice containing these 2 product information. I have marked purchased invoices and refunded invoices in the same color.
I have also added the same data as text below:
CustomerCode InvoiceNumber IsReturn InvoiceDate ApplicationID ItemCode ColorCode ItemDim1Code Qty Revenue
15656858 193-R-7-373740 1 11.06.2022 DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F 4A1400000002 SYH 50 -1 -699,99
15656858 193-R-7-372193 0 27.05.2022 DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F 4A1400000002 SYH 50 2 1.399,98
15656858 193-R-7-373781 1 12.06.2022 D7BD6F42-F1F3-4EC4-9599-AEB000A7CB70 4A2022200036 YVZ XL -1 -240,28
15656858 193-R-7-373502 0 10.06.2022 D7BD6F42-F1F3-4EC4-9599-AEB000A7CB70 4A2022200036 YVZ XL 1 240,28
15656858 193-R-7-372193 0 27.05.2022 DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F 4A7020000001 NAT STN 1 26,95
15656858 193-R-7-373782 0 12.06.2022 D832836F-36BB-42ED-B64C-AEB200B6B19F 4A2022200001 BYD L 1 299,99
15656858 193-R-7-373782 0 12.06.2022 D832836F-36BB-42ED-B64C-AEB200B6B19F 4A2022200034 HAK XL 1 299,99
15656858 193-R-7-373742 0 11.06.2022 00CB6240-D073-4E06-8CA9-AEB10133DE90 4A1400000001 SYH 50 1 699,99
15656858 193-R-7-373742 0 11.06.2022 00CB6240-D073-4E06-8CA9-AEB10133DE90 4A1400000001 ATR 52 1 699,99
21069880 406-R-7-121023 0 4.05.2023 67732FA9-E3CA-4BB1-8C13-AFF800F89C60 4A0121100002 YSL 52 1 199,99
21069880 406-R-7-120741 0 26.04.2023 284DE832-8CE7-41D1-9FCE-AE31011A4AA8 4A1400000001 SYH 56 1 379,99
21069880 1-R-7-16894399 1 26.04.2023 284DE832-8CE7-41D1-9FCE-AE31011A4AA8 4A1400000001 SYH 56 -1 -379,99
21069880 406-R-7-120740 0 26.04.2023 9E1674D1-8B70-4BCF-9A82-AFF00112E876 4A1400000001 SYH 52 1 879,99
21069880 1-R-7-16853468 0 3.09.2022 F045ED37-E17C-4690-83D9-AF0500A17621 4A2000000013 amv XL 3 1.999,86
21069880 1-R-7-16894396 1 7.09.2022 F045ED37-E17C-4690-83D9-AF0500A17621 4A2000000013 amv XL -1 -666,62
21069880 1-R-7-16853468 0 3.09.2022 F045ED37-E17C-4690-83D9-AF0500A17621 4A3022100145 GSY 52 1 1.587,20
21069880 406-R-7-120740 0 4.05.2023 8C5D271E-30D2-497E-BB0C-AFF800F9EE0B 4A9000000001 GMJ L 1 359,99
In the end I would like to get an output like the following:
2