Cash matching
This is a cash matching problem. You can track this at one of two levels:
Compare invoiced to cash figures (somewhat sloppy but this is actually how it's done for inwards business by most Lloyd's Syndicates, often called a 'written vs. signed' report).
Maintain explicit cash allocations from cash payments broken down by invoice.
From your question I think you want to do the latter.
Typically this is done by having a separate set of cash transactions, and a bridging table that has the allocation of cash payments to invoices. If the values are equal or the cash payment comes with a single invoice reference you can do the allocation automatically. If there's a M:M relationship between invoices and payments you will need to do a manual matching process (doing this automatically is actually a variant of the knapsack problem).
A basic cash matching system
Imagine that you have an invoice table, a cash payments table and an allocation table. When you issue an invoice then you set up an invoice record in the invoices table and a 'receivable' or 'payable' record in the allocations table.
Now, you get a cash payment of $100
Cash payments (chq #12345): $100
Allocation: a record with a reference to invoice #1 and chq #12345, 'cash' transaction type and -100 owing ($100 paid).
You can generalise this to a M:M relationship where you get multiple payments against a single invoice or a payment covering multiple invoices. This structure also makes it quite easy to build credit control reports. The report just needs to find invoices older than (say) 180 days that still have outstanding balances.
Here's an example of the schema plus a couple of scenarios and an aged debt query. Unfortunately I don't have a running mysql instance to hand, so this one is for SQL Server.
-- ==============================================================
-- === CashMatch.sql ============================================
-- ==============================================================
--
-- === Invoices =================================================
--
create table Invoice (
InvoiceID int identity (1,1) not null
,InvoiceRef varchar (20)
,Amount money
,InvoiceDate datetime
)
go
alter table Invoice
add constraint PK_Invoice
primary key nonclustered (InvoiceID)
go
-- === Cash Payments ============================================
--
create table CashPayment (
CashPaymentID int identity (1,1) not null
,CashPaymentRef varchar (20)
,Amount money
,PaidDate datetime
)
go
alter table CashPayment
add constraint PK_CashPayment
primary key nonclustered (CashPaymentID)
go
-- === Allocations ==============================================
--
create table Allocation (
AllocationID int identity (1,1) not null
,CashPaymentID int -- Note that some records are not
,InvoiceID int -- on one side.
,AllocatedAmount money
,AllocationType varchar (20)
,TransactionDate datetime
)
go
alter table Allocation
add constraint PK_Allocation
primary key nonclustered (AllocationID)
go
-- ==============================================================
-- === Scenarios ================================================
-- ==============================================================
--
declare @Invoice1ID int
,@Invoice2ID int
,@PaymentID int
-- === Raise a new invoice ======================================
--
insert Invoice (InvoiceRef, Amount, InvoiceDate)
values ('001', 100, '2012-01-01')
set @Invoice1ID = @@identity
insert Allocation (
InvoiceID
,AllocatedAmount
,TransactionDate
,AllocationType
) values (@Invoice1ID, 100, '2012-01-01', 'receivable')
-- === Receive a payment ========================================
--
insert CashPayment (CashPaymentRef, Amount, PaidDate)
values ('12345', 100, getdate())
set @PaymentID = @@identity
insert Allocation (
InvoiceID
,CashPaymentID
,AllocatedAmount
,TransactionDate
,AllocationType
) values (@Invoice1ID, @PaymentID, -100, getdate(), 'paid')
-- === Raise two invoices =======================================
--
insert Invoice (InvoiceRef, Amount, InvoiceDate)
values ('002', 75, '2012-01-01')
set @Invoice1ID = @@identity
insert Allocation (
InvoiceID
,AllocatedAmount
,TransactionDate
,AllocationType
) values (@Invoice1ID, 75, '2012-01-01', 'receivable')
insert Invoice (InvoiceRef, Amount, InvoiceDate)
values ('003', 75, '2012-01-01')
set @Invoice2ID = @@identity
insert Allocation (
InvoiceID
,AllocatedAmount
,TransactionDate
,AllocationType
) values (@Invoice2ID, 75, '2012-01-01', 'receivable')
-- === Receive a payment ========================================
-- The payment covers one invoice in full and part of the other.
--
insert CashPayment (CashPaymentRef, Amount, PaidDate)
values ('23456', 120, getdate())
set @PaymentID = @@identity
insert Allocation (
InvoiceID
,CashPaymentID
,AllocatedAmount
,TransactionDate
,AllocationType
) values (@Invoice1ID, @PaymentID, -75, getdate(), 'paid')
insert Allocation (
InvoiceID
,CashPaymentID
,AllocatedAmount
,TransactionDate
,AllocationType
) values (@Invoice2ID, @PaymentID, -45, getdate(), 'paid')
-- === Aged debt report ========================================
--
select i.InvoiceRef
,sum (a.AllocatedAmount) as Owing
,datediff (dd, i.InvoiceDate, getdate()) as Age
from Invoice i
join Allocation a
on a.InvoiceID = i.InvoiceID
group by i.InvoiceRef
,datediff (dd, i.InvoiceDate, getdate())
having sum (a.AllocatedAmount) > 0
The two queries have a very big difference:
----- query 1
SELECT COUNT(*)
FROM customers
WHERE ID > 10000
AND country = 'US' ;
----- query 2
SELECT *
FROM customers
WHERE ID > 10000
AND country = 'US' ;
While the second query returns all rows that match the WHERE
conditions, the first one has an aggregate function (COUNT()
) in the SELECT
list, so it does an aggregation, a collapsing of rows that match the conditions into one row and returns only one number, the number of rows that match the conditions.
So, for the first query, there is no sensible reason to have an ORDER BY
. The result is one row only. Even more, it should produce an error as the rows (that have been collapsed into one) may have different values in the country
and created_at
columns. So, which one should be used for the ordering (say in a case where you had a GROUP BY
and the result set was more than one rows)?
You can test at SQL-Fiddle that SQL-Server, when you add ORDER BY country, created_at
, it produces the error:
Column "customers.country" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
An error is produced in Postgres, too.
But even in MySQL that may allow such non-standard syntax, to add ORDER BY
in the first query, the optimizer is smart enough to not take that into account for the execution plan. There is nothing to order. One row will be returned anyway. You can check that by viewing the execution plans with EXPLAIN
. Simple test at SQL-Fiddle: Mysql-test
Oracle (version 11g2) seems to allow such nonsense too. You can see the execution plan here: Oracle-test. Not sure how the plan should be interpreted but it seems that Oracle at least knows that it's one row only so the "sorting" operation is not costly.
Best Answer
If you
LEFT JOIN
the tables you can then check if the leftordno
does not exist. Something like this:I hope this helps you.