TableA
Product    Quantity
abc        5
abc        10
abc        15
xyz        10

TableB
Product    Units
abc        10
abc        5
def        10

Product     Quantity        Unit      Difference
abc         30              15        15
xyz         10                        10
def                         10        10

Select Case when Product is null then Product1 else product end as Product,Sum(Distinct Quantity) as Quantity,
Sum(Distinct units) as Units ,Abs(Isnull(Sum(Distinct Quantity),0) - Isnull(Sum(Distinct units),0))  as Diff from 
	(
	Select T1.*, T2.Product as product1, T2.units from TableA T1 
	Full  outer join TableB T2 on 	T1.product=T2.product
	) T 
group by Product,Product1 order by Product

Select Product, Sum(Units) as Units, Sum(Quantity) as Qty, sum(Units) - sum(Quantity) as Diff
from
(
   select Product, 0 as Units, Quantity
   from TableA
   union all
   select Product, Units, 0 as Quantity
   from TableB
) a
group by Product

quote:

---

Originally posted by chiragkhabaria

Aha.. thanks.. will keep in mind next time.. :-)..

by the way can you let me know what is wrong with the Full outer join .. ???

Complicated things can be done by simple thinking

---

quote:

---

Originally posted by madhivanan

>>Meanwhile I got my solution. Might not be an optimal way...

I think jeff's is more optimal
Can you post your solution?

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by jsmith8858

No full outer joins, please!
(you know I hate those!)

It doesn't seem like the obvious answer, but a very simple and quick way to get the answer is with a UNION:

Select Product, Sum(Units) as Units, Sum(Quantity) as Qty, sum(Units) - sum(Quantity) as Diff
from
(
   select Product, 0 as Units, Quantity
   from TableA
   union all
   select Product, Units, 0 as Quantity
   from TableB
) a
group by Product

---

quote:

---

Originally posted by madhivanan

>>Meanwhile I got my solution. Might not be an optimal way...

I think jeff's is more optimal
Can you post your solution?

Madhivanan

Failing to plan is Planning to fail

---

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestTblKK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TestTblKK]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestTblKK1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TestTblKK1]
GO

CREATE TABLE [dbo].[TestTblKK] (
	[Products] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Quantity] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TestTblKK1] (
	[Products] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Units] [int] NOT NULL 
) ON [PRIMARY]
GO

INSERT INTO TestTblKK VALUES('ABC',5)
INSERT INTO TestTblKK VALUES('ABC',10)
INSERT INTO TestTblKK VALUES('ABC',15)
INSERT INTO TestTblKK VALUES('XYZ',10)
INSERT INTO TestTblKK1 VALUES('ABC',5)
INSERT INTO TestTblKK1 VALUES('ABC',10)
INSERT INTO TestTblKK1 VALUES('DEF',10)



SELECT PROD,QUANTITY,UNITS, ISNULL(QUANTITY,0)-ISNULL(UNITS,0) AS VARIANCE FROM 
(
SELECT PRODUCTS AS PROD ,SUM(QUANTITY) AS QUANTITY,UNITS  FROM TestTblKK
LEFT JOIN 
(Select PRODUCTS AS PROD,SUM(UNITS) AS UNITS FROM TestTblKK1 GROUP BY PRODUCTS --WHERE 
) T
ON  PRODUCTS= PROD --WHERE 
GROUP BY PRODUCTS,UNITS) T2 WHERE ISNULL(UNITS,0)-ISNULL(QUANTITY,0) !=0
GROUP BY PROD, QUANTITY, UNITS 
UNION

SELECT PROD,QUANTITY,UNITS, ISNULL(QUANTITY,0)-ISNULL(UNITS,0) AS VARIANCE FROM 
 (
SELECT PRODUCTS AS PROD ,SUM(UNITS) AS UNITS,QUANTITY  FROM TestTblKK1
LEFT JOIN 
(Select PRODUCTS AS PROD,SUM(QUANTITY) AS QUANTITY FROM TestTblKK  
GROUP BY PRODUCTS) T
ON PROD = PRODUCTS GROUP BY PRODUCTS,QUANTITY) T2 WHERE ISNULL(UNITS,0)-ISNULL(QUANTITY,0)  != 0
GROUP BY PROD, UNITS, QUANTITY

quote:

---

Originally posted by jsmith8858

My motto is always: simple = good !

Glad I could help give you an easier solution that also makes sense.

---