#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0

    Joining invoices and payments - seems like a simple problem


    Here's a challenge. In the accounts system I've developed I'd like to get a list of outstanding invoices.

    I'm trying to do this with three linked tables:

    The invoices table has fields including invoicenumber, date, order, customer etc
    The invoiceitems table has fields including iteminvoicenumber, description, itemamount etc.
    The payments table has fields including paymentinvoicenumber, paymentref, paymentamount etc.

    The three tables are joined on the following fields: invoicenumber - iteminvoicenumber - paymentinvoicenumber

    For each invoice there may be several invoiceitems and several payments

    I'd first like to produce a list which shows the balance of each invoice.

    Here's my code which doesn't quite work.

    select sum(itemamount)-sum(paymentamount) as invoicebalance, invoicedate, invoicenumber
    from invoices
    join invoiceitems
    on iteminvoicenumber=invoicenumber
    join payments
    on paymentinvoicenumber=invoicenumber
    group by invoicenumber
    order by invoicenumber

    The problem with this is that if there is more than one invoice item the payment is duplicated for each. I'm not sure if this can be solved by using left/right/inner/outer joins or if something more is needed.

    Any help gratefully received!

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    here's how to do it by using subqueries to aggregate both the item amounts and the payment amounts
    Code:
    SELECT i.invoicenumber
         , i.invoicedate 
         , ia.sum_itemamount - p.sum_paymentamounts AS invoicebalance
      FROM invoices AS i
    INNER
      JOIN ( SELECT iteminvoicenumber 
                  , SUM(itemamount) AS sum_itemamounts
               FROM invoiceitems
             GROUP
                 BY iteminvoicenumber ) AS ia
        ON ia.iteminvoicenumber = i.invoicenumber
    INNER    
      JOIN ( SELECT paymentinvoicenumber
                  , SUM(paymentamount) AS sum_paymentamounts
               FROM payments
             GROUP
                 BY paymentinvoicenumber ) AS p
        ON p.paymentinvoicenumber = i.invoicenumber
    ORDER 
        BY i.invoicenumber
    simple, yes?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0

    Nearly there!


    Hi Rudy

    Thanks for your swift and brilliantly simple reply!
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0

    Nearly there!


    Hi Rudy

    Thanks for your swift and brilliantly simple reply! There are several principles in this which are new to me so it is also a useful learning opportunity for me.

    One problem - it only lists the invoices with zero balances (paid) or those that are partially paid. ie if there is no payment record against an invoice then that invoice is not listed. I am rather more interested in the invoices which have a non-zero balance!

    This could be my fault as I had simplified my example to demonstrate the problem and had changed some of the field names to make them more intuitive.

    Here is the actual code I ended up with thanks to you, which runs with no errors:

    SELECT i.invoiceid
    , i.invoicedate
    , ia.sum_itemamounts - p.sum_paymentamounts AS invoicebalance
    FROM sa_invoices AS i
    INNER
    JOIN ( SELECT trinvoiceid
    , SUM(tramt+(trvatrate/100*tramt)) AS sum_itemamounts
    FROM sa_invoiceitems
    GROUP
    BY trinvoiceid ) AS ia
    ON ia.trinvoiceid = i.invoiceid
    INNER
    JOIN ( SELECT pinvoiceid
    , SUM(pamt) AS sum_paymentamounts
    FROM sa_payments
    GROUP
    BY pinvoiceid ) AS p
    ON p.pinvoiceid = i.invoiceid
    ORDER
    BY i.invoiceid

    I should have said in my original post: For each invoice there may be several invoiceitems and several payments, or no payments.


    One slight further refinement, if we can get this to list every invoice balance, will be to leave out the zero balance ones, ie the invoices which have been paid. Hopefully I can add somewhere:

    WHERE invoicebalance <> 0

    ??

    Many thanks for your help so far.

    David
    Last edited by bamboozled; December 11th, 2012 at 04:40 PM. Reason: Missing part
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Originally Posted by bamboozled
    One slight further refinement, if we can get this to list every invoice balance, will be to leave out the zero balance ones
    here you go, sir --
    Code:
    SELECT i.invoiceid
         , i.invoicedate 
         , ia.sum_itemamounts - COALESCE(p.sum_paymentamounts,0) AS invoicebalance
      FROM sa_invoices AS i
    INNER
      JOIN ( SELECT trinvoiceid 
                  , SUM(tramt+(trvatrate/100*tramt)) AS sum_itemamounts
               FROM sa_invoiceitems
             GROUP
                 BY trinvoiceid ) AS ia
        ON ia.trinvoiceid = i.invoiceid
    LEFT OUTER    
      JOIN ( SELECT pinvoiceid
                  , SUM(pamt) AS sum_paymentamounts
               FROM sa_payments
             GROUP
                 BY pinvoiceid ) AS p
        ON p.pinvoiceid = i.invoiceid
     WHERE ia.sum_itemamounts <> COALESCE(p.sum_paymentamounts,0)    
    ORDER 
        BY i.invoiceid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0

    Case Solved!


    Hi Rudy

    That works perfectly! This is the first time in 20 years I've ever asked for help on a forum - I always try hard to work things out for myself, but this problem had eluded me for months.

    Thanks very much!

    Best wishes

    David
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Usenet aside, the earliest web-based forums date from the mid-1990s, so let's call it 18 years.

    I'll get my coat...

    :-)

IMN logo majestic logo threadwatch logo seochat tools logo