# Thread: Combined query with calculation

1. Registered User
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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.

3. Registered User
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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

5. Registered User
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. Registered User
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. Registered User
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
•