> SELECT TotalInvoice, (SELECT SUM(PaymentValue) FROM Payments
> WHERE Payments.IDInvoice = IDInvoice) AS TotalPaid, TotalPaid = TotalInvoice
> AS FullyPaid FROM Invoices;
>
> Here, I select:
> - TotalInvoice the total amount of the invoice
> - TotalPaid the total amount paid till now
> - FullyPaid a boolean flag indicating if the invoice is paid or not
select TotalInvoice, paid.totalpaid, (paid.totalpaid = TotalInvoice) as
FullyPaid
from Invoices,
(
select Payments.IDInvoice as IDInvoice, sum(PaymentValue) as
totalpaid
from Payments
where Payments.IDInvoice = Invoices.IDInvoice
group by payments.idinvoice
) as paid
where Invoices.IDInvoice = paid.IDInvoice;
Haven't checked that in any way, but it seems to be what you wanted. Things
like that work in MS SQL Server 2000, though the boolean thing is something
I've never tried. Worse comes to worse, you can always do that part in your
code, which is where (*my* DBA says, anyway) it belongs anyway, since it's
application logic and not data logic. :-)
You could also do it as a join if you wanted to.
Brad
