0

Two tables: "S" (for "Services") and "Payments". They're tied together via the "S"."ID-S" being referenced as a foreign key: "Payments"."S".

I'm trying to create a query for a dropdown list in an "add payment" form, but since I hope to allow for partial payments, I want to substract the sum of the payments already made that are tied to the given "ID-S". However, I can't seem to hide the services paid off with multiple payments.

The following is my current code, using just one of the category of "services" (in this case the invoices):

SELECT "S"."Invoice" || ' for $' || "S"."Price" -
IIF("Payments"."S" IS NULL, 0, SUM("Payments"."Amount")
AS "To-Pay", "ID-S"
FROM "S"
LEFT OUTER JOIN "Payments" ON "S"."ID-S" = "Payments"."S"
GROUP BY "S"."Invoice", "S"."Price", "Payments"."Amount", "Payments"."S", "S"."ID-S"
HAVING "Payments"."S" IS NULL OR
SUM("Payments"."Amount") < "S"."Price"

It's supposed to show, for example:

  • 1/1/2018 for $400
  • 2/1/2018 for $1050
  • 6/8/2018 for $750

etc, with the price being the remaining price to pay. It's not supposed to show completely paid off invoices at all.

As far as I can tell, the problem is that the SUM("Payments"."Amount") doesn't sum all the "Amount" columns with a matching "S-ID", it only takes the "Amount" from the given row. This doesn't help me one bit, because if, say, a $3000 invoice is paid off with two payments of $1000 and $2000 respectively, the list will not only still show the invoice, but show it twice - once with $2000 remaining and once with $1000 remaining.

I'm using LibreOffice Base with Firebird.

EDIT: Upon some further data input, I've found one case of double payment being displayed as mentioned above (two times with different "prices remaining"), yet another such double payment displays once with "$0" price, perhaps due to being split exactly half-in-half?

Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158
eworm
  • 27
  • 6
  • 1
    Please, show the tables with sample data in the. – Arioch 'The Oct 30 '18 at 12:17
  • see this answer for the incremental process of growing a query from a simplistic seed to more and more complex plant. While the task is not yours, the very process could be well the same: http://stackoverflow.com/a/51398120/976391 – Arioch 'The Oct 31 '18 at 08:00

1 Answers1

0

Perhaps you should extract/encapsulate grouping into a separate query. It is hard to reason without seeing example data, but try to do use either Derived Table or Common Table Expression.

Below are just topmost google results to give you a general idea.

Firebird-specific manual is at https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html

Also - do you really need it all done on the server, like concatenating text strings? Usually it is made on-client with localization and font decorating and what not.

Pattern to try:

WITH Parts AS ( 
  SELECT ID_S, Sum(Amount) as Done 
  FROM Payments 
  GROUP BY 1
) 
SELECT S.Invoice, S.Price - COALESCE( Parts.Done, 0)
FROM Services S
LEFT JOIN Parts ON S.ID = Parts.ID_S
-- WHERE S.Price > COALESCE( Parts.Done, 0) 
WHERE S.Price > Parts.Done OR Parts.Done IS NULL

Or equal pattern

SELECT S.Invoice, S.Price - COALESCE( Parts.Done, 0)
FROM Services S
LEFT JOIN ( SELECT ID_S, Sum(Amount) as Done FROM Payments GROUP BY 1) 
     AS Parts ON S.ID = Parts.ID_S
-- WHERE S.Price > COALESCE( Parts.Done, 0) 
WHERE S.Price > Parts.Done OR Parts.Done IS NULL
Arioch 'The
  • 15,005
  • 31
  • 59