I have 2 tables, table A and Table B, and I want to check if Table B matches quantity of items that exists on Table A, so, for example:

Table A

Table B

My first thought was to use an except between the two tables, but then I was asked to check if the quantity was OK or if it was shortage to generate a preview like:

Result from comparing the two tables

And it needs to ignore items "ITE" and "ITF" because they don't exist in Table A I'm pretty new with sql server queries, I think I could use a SELECT CASE but I don't know how to do it, I'd appreciate some help in this matter In those tables my unique identifier is S_O, so it would need to match the S_O, item and quantity for both tables

CodePudding user response：

Here's how you can "pre summarise" table a and b to make item unique, then join:

select 
  A.item, 
  A.qty as qtya, 
  B.qty as qtyb, 
  A.qty - B.qty as shortageamt,
  case 
    when A.qty = B.qty then 'OK' 
    else 'Shortage' 
  end as status
from
(
  select item, sum(qty) as qty
  from tablea
  group by item
) as A
inner join
(
  select item, sum(qty) as qty
  from tableb
  group by item
) as B
on A.item = B.item

You'll only get an item listed in the result if it's in both tables - is that what you want? if ITG is in tablea but not tableb, do you want to see it (with qty 0)? That requires an outer join.

CodePudding user response：

I think you should go with a LEFT JOIN. Also, the first answer does not JOIN on S_O, which you state in your question you need. "In those tables my unique identifier is S_O, so it would need to match the S_O, item and quantity for both tables"

DECLARE @ta TABLE (S_O INT, ITEM VARCHAR(20), QTY INT)
INSERT INTO @ta
VALUES
(1, 'ITA',  1),
(3, 'ITB',  2),
(4, 'ITC',  3),
(6, 'ITD',  0)


DECLARE @tb TABLE (S_O INT, ITEM VARCHAR(20), QTY INT)
INSERT INTO @tb
VALUES
(1, 'ITA',  1),
(3, 'ITB',  2),
(4, 'ITC',  3),
(6, 'ITD',  5),
(7, 'ITE',  2),
(8, 'ITF',  1);

SELECT ta.S_O,
    ta.ITEM,
    CASE WHEN ta.ITEM = tb.ITEM AND SUM(ta.QTY) >= SUM(tb.QTY) THEN 'OK' ELSE 'SHORTAGE' END AS 'STATUS'
FROM @ta ta
    LEFT JOIN @tb tb ON ta.S_O = tb.S_O
GROUP BY ta.S_O, ta.ITEM, tb.ITEM