Find difference between two sets of records

Question

I have a set of data on sql server something like:

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

It means that a customer ordered 10 carrots and 8 onions (on one invoice) but actually received only 5 carrots and 4 onions. If status is null then it is original quantity, if status is C then it is corrected quantity

I need to generate 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 ordered quantity and real quantity on each invoice. I have no idea how to begin. Any help deeply appreciated :)

Answer 1

Option with 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

Demo on SQLFiddle

Answer 2

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 it working here: http://sqlfiddle.com/#!4/f96adf/14