Find the difference between two record sets

Question

I have a dataset on sql server, for example:

ID ID_Invoice Article Quantity Status 1 10 carrot 10 null 2 10 carrot 5 C 3 10 onion 8 null 4 10 onion 4 C 5 11 tomato 20 null 6 11 tomato 18 C 7 11 onion 2 null 8 11 onion 1 C 

This means that the client ordered 10 carrots and 8 onions (one account each), but actually received only 5 carrots and 4 onions. If the status is null, then this is the original value, if the status is C, then the quantity is adjusted

I need to create a table like

 ID ID_Invoice Article Quantity 1 10 carrot -5 2 10 onion -4 3 11 tomato -2 4 11 onion -1 

which shows the difference between the ordered quantity and the real value in each account. I do not know how to start. Any help is greatly appreciated :)

Answer 1

Option with a simple CASE expression without excessive JOIN

 SELECT ID_Invoice, Article, SUM(CASE WHEN Status IS NULL THEN -1 * Quantity ELSE Quantity END) AS Quantity FROM dbo.test38 GROUP BY ID_Invoice, Article 

Result:

 ID_Invoice Article Quantity 10 carrot -5 10 onion -4 11 onion -1 11 tomato -2 

SQLFiddle Demo

Answer 2

You did not specify which RDBMS you are using, but my answer is in accordance with the ANSI-SQL standard. Works with every existing RDBMS.

 SELECT yt1.ID_Invoice, yt1.Article, yt2.Quantity - yt1.Quantity AS Quantity FROM yourTable yt1 INNER JOIN yourTable yt2 ON yt1.ID_Invoice = yt2.ID_Invoice AND yt1.Article = yt2.Article AND yt2.Status = 'C' WHERE yt1.Status IS NULL 

This answer always contains a record with the status NULL and the corresponding line with the status 'C'. If this is not the case, you will have to configure it as follows:

 SELECT yt1.ID_Invoice, yt1.Article, CASE WHEN yt2.Quantity IS NULL THEN yt1.Quantity ELSE yt2.Quantity - yt1.Quantity END AS Quantity FROM yourTable yt1 LEFT JOIN yourTable yt2 ON yt1.ID_Invoice = yt2.ID_Invoice AND yt1.Article = yt2.Article AND yt2.Status = 'C' WHERE yt1.Status IS NULL 

Answer 3

Least Resource-intensive:

 SELECT id_invoice , article , org_quantity , new_quantity , new_quantity - org_quantity diff FROM (SELECT id_invoice , article , max(CASE WHEN status IS NULL THEN quantity else null END) org_quantity , max(CASE WHEN status = 'C' THEN quantity else null END) new_quantity FROM orders GROUP BY id_invoice , article) 

See how it works: http://sqlfiddle.com/#!4/f96adf/14

Answer 4

So, first of all, you need to separate the actual from the order by making 2 requests, and then you have to join the actual orders .. something like this

 select Recived.ID, Recived.ID_Invoice, Recived.Article, Recived.Quantity - Ordered.Quantity as Quantity from (select * from dataTable where Status is null) as Ordered left join (select * from dataTable where Status = 'C') as Recived on (Ordered.ID_Invoice = Recived.ID_Invoice and Ordered.Article = Recived.Article ) 

ATTENTION! you will be better off if you have an identifier for each article to use in the "left join" instead of comparing varchars.

Here is an example script: http://sqlfiddle.com/#!2/16666/1