CODEWITHSUNDEEP
sql-servert-sql
Michal
Michal

Reputation: 5913

Finding invoices without matching credits

The simplified table looks like that:

BillID|ProductID|CustomerID|Price|TypeID
------+---------+----------+-----+-------
111111|Product1 |Customer1 |  100| I
111112|Product1 |Customer1 | -100| C
111113|Product1 |Customer1 |  100| I
111114|Product1 |Customer1 | -100| C
111115|Product1 |Customer1 |  100| I

I need to find invoices (I) that have their matching credits (C) but not "odd" invoices without matching credits (the last record) - or the other way around (unmatched invoices without corresponding credits).

So far I've got this:

SELECT Invoices.billid, Credits.billid
FROM
    (SELECT B1.billid
    FROM billing B1
    WHERE B1.typeid='I') Invoices
INNER JOIN
    (SELECT B2.billid
    FROM billing B2
    WHERE B2.typeid='C') Credits
ON Invoices.customerid = Credits.customerid
    AND Invoices.productid = Credits.productid
    AND Invoices.price = -(Credits.price)

But it obviously doesn't work, as it returns something looking like:

billid | billid2
-------+ -------
111111 | 111112
111113 | 111114
111115 | 111114

What I would like to get is a list of unmatched invoices;

billid |
-------+
111115 |

Or alternatively only the matching invoices;

billid | billid2
-------+ -------
111111 | 111112
111113 | 111114

The invoice numbers (BillID) will not necessarily be consecutive of course, it's just a simplified view.

Any help would be appreciated.

Upvotes: 0

Views: 268

Answers (3)

Mike Baron
Mike Baron

Reputation: 858

This should work. I tested by adding a few consecutive invoices before a credit. The query below shows all invoices with matching credit and shows NULL for the aliased "bar" part of the query if a match doesn't exist.

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER(Partition By TypeID, CustomerID, ProductID, Price ORDER BY BillID ASC) AS rownumber,
    *
  FROM Billing
)  AS foo
LEFT JOIN 
  (SELECT
    ROW_NUMBER() OVER(Partition By TypeID, CustomerID, ProductID, Price ORDER BY BillID ASC) AS rownumber,
    *
  FROM Billing
) AS bar
on foo.CustomerID = bar.CustomerID and 
foo.ProductID = bar.ProductID and 
foo.rownumber = bar.rownumber and 
foo.Price = -1*bar.Price
where foo.Price > 1

Here's the updated data that I used: data

And Here are what my results looked like:
enter image description here

Upvotes: 3

Dale K
Dale K

Reputation: 27249

I wrote this a long time ago so there may be better ways to solve it now. Also I've attempted to adapt it to your table structure, so apologies if its not 100% there. I also assume that your BillID is sequential in date order i.e. larger numbers were entered later. I've also assumed that invoices are always positive and credit notes always negative - so I don't bother checking the type.

Essentially the query filters out any matched items.

Anyway here goes:

select *
from billing X
/* If we are inside the number of unmatched entries then show it. e.g. if there are 3 unmatched entries, and we are in the top 3 then display */
where (
  /* Number of later entries relating that match this account entry e.g. Price/Product/Customer */
  select count(*)
  from billing Z
  where Z.Customer = X.Customer and Z.ProductID = X.ProductID
  and Z.Price = X.Price
  and Z.BillID >= X.BillId
) <=
(
  /* Number of unmatched entries for this Price/Product/Customer there are, and whether they are negative or positive. */
  select abs(Y.Number)
  from (
    -- Works out how many unmatched billing entries for this Price/Product/Customer there are, and whether they are negative or positive
    select ProductID, CustomerID, abs(Price) Price, sum(case when Price < 0 then -1 else +1 end) Number
    from billing
    group by ProductID, CustomerID, abs(Price)
    having sum(Price) <> 0
  ) as Y
  where X.ProductID = Y.ProductID
  and X.CustomerID = Y.CustomerID
  and X.Price = case when Y.Number < 0 then -1*Y.Amount else Y.Amount end
)

Upvotes: 1

Andrew
Andrew

Reputation: 373

The odd/even thing concerns me a bit. But assuming this is an incremental key and your business logic is in place, try including this logic in the WHERE clause, the JOIN PREDICATE, or implementing a Lead/Lag function.

SELECT DISTINCT
 Invoices.billid
,Credits.billid

FROM
    (SELECT B1.billid
    FROM billing B1
    WHERE B1.typeid='I') Invoices
INNER JOIN (SELECT B2.billid
    FROM billing B2
    WHERE B2.typeid='C') Credits
ON Invoices.customerid = Credits.customerid
    AND Invoices.productid = Credits.productid
    AND Invoices.price = -(Credits.price)
    AND (Invoices.Billid + 1) = Credits.Billid

Note: This is using your INNER JOIN, so we will get the cases where the invoices have a corresponding credit. You could also do a FULL OUTER JOIN instead, then include a WHERE CLAUSE that specifies WHERE Invoices.Billid IS NULL OR Credits.Billid IS NULL. That scenario would give you the trailing case where you don't have a match.

Upvotes: 1

Related Questions