Remove duplicate rows for AR BIP report to improve performance
I've created a query that pulls all AR transactions for a parameterized transaction date and matches our IBAN record in the customer with any IBAN found in the bank line description of the reconciled Cash Management line. We use this for AML transaction monitoring in our main process.
The query runs fine and gives the desired result (feel free to use it ), but I have an issue with the query returning a LOT of rows for each record. I 'fixed' this by adding a DISTINCT to the SELECT clause. However, this is very bad for the performance of the query. It takes a few minutes to return ~200 records.