Hi frnds need help regarding a query to compare two resultset and
highlight those record which are not matching.
-----------------------------------------------------------------------------------------------------------------------------------------
Table A - ItemMaster
ID | ItemCode | ItemSubCode | Qty | UsedQty |
++++++++++++++++++++++++++++++++++
1 | I1 | H1 | 20 | 13 |
2 | I1 | H2 | 10 | 9 |
3 | I1 | H3 | 30 | 0 |
4 | I2 | H1 | 5 | 3 |
5 | I2 | H4 | 15 | 0 |
6 | I3 | H1 | 25 | 2 |

Table B - Orders
OrderID | ItemCode | ItemSubCode | OrderQty |
++++++++++++++++++++++++++++++++++
1 | I1 | H1 | 6 |
2 | I1 | H2 | 4 |
3 | I1 | H1 | 7 |
4 | I2 | H1 | 2 |
5 | I1 | H2 | 5 |
6 | I3 | H1 | 1 |

Table C - Result I want On displaying those records whose Flag value
is - "1"
B.ItemCode | B.ItemSubCode | B.OrderQty | A.ItemCode | A.ItemSubCode |
A.UsedQty | Flag |
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++
I1 | H1 | 13 |
I1 | H1 | 13 | 0 |
I1 | H2 | 9 |
I1 | H2 | 9 | 0 |
I2 | H1 | 2 |
I2 | H1 | 3 | 1 |
I3 | H1 | 1 |
I3 | H1 | 2 | 1 |
--------------------------------------------------------------------------------------------------------------------------------------
No what I want to find out is I want to compare the sum(OrderQty) of
Table B should be equal to sum(UsedQty) of Table A on Same ItemCode
and Same ItemSubcode and only those ItemCode & ItemSubCode present in
Table B also my resultset should be only those combination where both
sum Qty is not matching as above in Table C out put.
What I m currently doing is first I m summing the records of Table B
and taking its result in Excel then summing Table A those records
which are present in Table B and taking that result in same excel and
then comparing both resultset using formula in which record I m
getting mismatch to find out where my transaction are getting wrong
but that is tedious job I want to make a query where in I get the
Table C as output.
Please frnd reply..

Declare @ItemMaster Table (ID int, ItemCode char(2), ItemSubCode char(2),
Qty int, UsedQty int);
Insert @ItemMaster (ID, ItemCode, ItemSubCode, Qty, UsedQty)
Select 1, 'I1', 'H1', 20, 13
Union All Select 2, 'I1', 'H2', 10, 9
Union All Select 3, 'I1', 'H3', 30, 0
Union All Select 4, 'I2', 'H1', 5, 3
Union All Select 5, 'I2', 'H4', 15, 0
Union All Select 6, 'I3', 'H1', 25, 2;

Declare @Orders Table (OrderID int, ItemCode char(2), ItemSubCode char(2),
OrderQty int);
Insert @Orders(OrderID, ItemCode, ItemSubCode, OrderQty)
Select 1, 'I1', 'H1', 6
Union All Select 2, 'I1', 'H2', 4
Union All Select 3, 'I1', 'H1', 7
Union All Select 4, 'I2', 'H1', 2
Union All Select 5, 'I1', 'H2', 5
Union All Select 6, 'I3', 'H1', 1;

With cteItem As
(Select ItemCode, ItemSubCode, Sum(UsedQty) As UsedQty
From @ItemMaster
Group By ItemCode, ItemSubCode),
cteOrders As
(Select ItemCode, ItemSubCode, Sum(OrderQty) As OrderQty
From @Orders
Group By ItemCode, ItemSubCode)
Select i.ItemCode, i.ItemSubCode, o.OrderQty, i.UsedQty,
Case When i.UsedQty <> o.OrderQty Then 1 Else 0 End As Flag
From cteItem i
Inner Join cteOrders o On i.ItemCode = o.ItemCode And i.ItemSubCode =
o.ItemSubCode
Order By i.ItemCode, i.ItemSubCode;

Tom

<chintu4uin (AT) gmail (DOT) com> wrote