CODEWITHSUNDEEP
sql
RRM
RRM

Reputation: 3451

Find difference between two sets of records

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 :)

Upvotes: 5

Views: 3508

Answers (4)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

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

Upvotes: 8

fancyPants
fancyPants

Reputation: 51868

You didn't specify, which RDBMS you're using, but my answer is ANSI-SQL standard compliant :) Works with every valid RDBMS out there.

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 is asuming, there's always a record with Status NULL and corresponding row with status 'C'. If this is not the case, you'd have to adjust it like this:

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

Upvotes: 2

JWK
JWK

Reputation: 492

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

Upvotes: 2

Mortalus
Mortalus

Reputation: 10712

So first of all you have to separate the actual from the ordered by making 2 queries and then you have to left join the orders to the actual .. 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 )

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

Here is a fiddle example: http://sqlfiddle.com/#!2/16666/1

Upvotes: 1

Related Questions