DECLARE @Invoices TABLE (InvoiceID int, CustId int, Vat float)
DECLARE @InvoiceLines TABLE (InvoiceID int, Price float, AddVat int)
DECLARE @Payments TABLE (InvoiceID int, Amount float)

INSERT @Invoices
   SELECT 1, 1, 25.0 UNION ALL
   SELECT 2, 2, 25.0
INSERT @InvoiceLines
   SELECT 1, 100.0, 1 UNION ALL 
   SELECT 1, 100.0, 0 UNION ALL 
   SELECT 2, 100.0, 1 UNION ALL 
   SELECT 2, 100.0, 0
INSERT @Payments
   SELECT 1, 50.0 UNION ALL
   SELECT 2, 225.0 

SELECT I.CustID
      ,I.InvoiceID
      ,(SELECT SUM(IL.Price * (100.0 + I.Vat * IL.AddVat) / 100.0)
        FROM @InvoiceLines AS IL
        WHERE (IL.InvoiceID = I.InvoiceID)
       ) AS Amount
      ,P.Payment
FROM @Invoices I
LEFT OUTER JOIN (
      SELECT InvoiceID, SUM(Amount) AS Payment
      FROM @Payments
      GROUP BY InvoiceID) P
   ON P.InvoiceID = I.InvoiceID

SELECT I.CustID
      ,I.InvoiceID
      ,(SELECT SUM(IL.Price * (100.0 + iI.Vat * IL.AddVat) / 100.0)
        FROM @Invoices iI
        LEFT OUTER JOIN @InvoiceLines IL
           ON IL.InvoiceID = iI.InvoiceID
        WHERE iI.InvoiceID = I.InvoiceID
       ) AS Amount
      ,P.Payment
FROM @Invoices I
LEFT OUTER JOIN (
      SELECT InvoiceID, SUM(Amount) AS Payment
      FROM @Payments
      GROUP BY InvoiceID) P
   ON P.InvoiceID = I.InvoiceID

quote:

---

Originally posted by byrmol

As an example, I have a view that ensures that a Invoice cannot be overpaid. All payments must pass through this view... Nothing a trigger couldn't do of course, but I prefer views over triggers for data integrity if it can be done in a view definition..

---