Outstanding balances for invoice table

Question

Outstanding balances for invoice table

jcp2mill

SSC Enthusiast

Points: 113
More actions
February 4, 2020 at 12:28 pm

#3722893

I have a table with cust references payment type pi or pa, amounts and transaction dates
The table design is actually not great because the if the incoming and outgoings transactions were always linked by reference then I could use that but unfortunately they're not and I have to work with what I have.
so given that i might have
cust1 pi 10.00 01/01/2020
cust1 pi 20.00 01/01/2020
cust2 pi 5.00 02/0/1/2020
cust1 pa -5.00 02/01/2020
cust2 pa -5.00 03/02/2020
cust 1 pi 1.00 03/02/2020
I'd like to show two entries for customer 1 for 1 and 10 and not show cust 2 at all because there balance is zero. The report that does it currently seems to do a running total for each customer in the table which as you can imagine is quote slow and I'd love to hear any ideas on a more efficient way of doing it .

Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

jcp2mill SSC Enthusiast Points: 113 More actions · Answer 1

I should mention I've already thought of summing by each customer to give me customers who have an outstanding balance but I'd like an efficient way of running through the resulting transactions to only give ones that are still outstanding.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Which method of running total is the report currently using?

Have you considered using SUM() OVER()?

Whichever method you're using, indexing will profoundly affect the query velocity.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden