Results 1 to 2 of 2
  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
    Apr 2012
    Posts
    213
    Try something like this:

    Code:
    Select 
        i.ID,
        i.Name,
        i.Date,
        i.Amount,
        i.Write-off,
        sum(case when p.Sales = true then credit else 0 end) as TotCredits,
        sum(case when p.Sales = true then debit  else 0 end) as TotDebits
    from invoice as i
    left outer join payments as p
        on p.InvoiceID = i.ID
    Where 
        year(i.date) = 2013
    group by
        i.ID,
        i.Name,
        i.Amount,
        i.Write-off
    having
        i.amount –
        sum(case when p.Sales = true then credit else 0 end) +
        sum(case when p.Sales = true then debit  else 0 end) -
        i.Write-off <> 0
    Hope this helps.

Posting Permissions

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