Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2013
    Posts
    6

    Unanswered: Combined query with calculation

    I need a query
    A have 2 tables invoice en payments
    Invoice: Number name amount write-off etc.
    Payment sales (Boolean) debit(amount) credit(amount) etc.
    I need a list with only the invoices which are not fully paid
    Select * from invoice
    left outer join payments on invoice.ID=payments.id
    Where year(date)=2013 and amount –sum(credit where payments.sales)+sum(debit where payments.sales)-write-off <>0

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Step 1: Write a query on the payments table that calculates the total amount paid (or credited) per invoice.


    give that a go and post back your attempts.
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2013
    Posts
    6

    step 1

    select invoiceID,sum(debit) debit,sum(credit) credit from journal
    where d_Cinvoice=1 and payed=0
    group by invoiceID

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have a play with this:
    Code:
    SELECT invoice.invoiceid
         , invoice.amount
         , payments_so_far.debit
         , payments_so_far.credit
    FROM   invoice
     LEFT
      JOIN (
            SELECT invoiceid
                 , Sum(debit) As debit
                 , Sum(credit) As credit
            FROM   journal 
            WHERE  d_cinvoice = 1
            AND    payed = 0
            GROUP
                BY invoiceid
           ) As payments_so_far
        ON payments_so_far.invoiceid = invoice.invoiceid
    I've taken step 1 and made it a subquery (wrapped it in brackets, given it al alias) then joined this to your invoice table.

    Let us know how you get on
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2013
    Posts
    6

    query

    Hi,
    I'm getting somewhere.
    Some ajustments here and there.
    (have to do some checking)
    But so far so good.
    Thanks

  6. #6
    Join Date
    Dec 2013
    Posts
    6

    query

    A problem occurs
    I can't calculate with a NULL
    Can I convert a NULL to 0 (zero)?

  7. #7
    Join Date
    Dec 2013
    Posts
    6

    query

    got it
    ISNULL(variant,0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •