I have 3 tables Company, Purchase and Sales.
CREATE table tblCompany (CompanyID int, Company varchar(50))
INSERT INTO tblCompany (CompanyID, Company) VALUES (1, 'ABC')
CREATE table tblPurchases (TranID int, CompanyID int, PurQty int)
INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (1, 1, 774)
INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (2, 1, 351)
INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (3, 1, 600)
INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (4, 1, 500)
INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (5, 1, 501)
CREATE table tblSales (TranID int, CompanyID int, SaleQty int)
INSERT INTO tblSales (TranID, CompanyID, SaleQty) VALUES (1, 1, 2726)
I want correct balance of quantities.
CompanyID, TPurQty, TSaleQty, BalanceQty
1 , 2726 , 2726 , 0
But It displays like this:
1 , 2726 ,13630 ,-10904
Where is the error in query?
What I have tried:
SELECT C.CompanyID, ISNULL(SUM(P.PurQty),0) TPurQty, ISNULL(SUM(S.SaleQty),0) TSaleQty
, (ISNULL(SUM(P.PurQty),0) - ISNULL(SUM(S.SaleQty),0)) BalanceQty FROM tblCompany C
FULL OUTER JOIN tblPurchases P ON C.CompanyID = P.CompanyID
FULL OUTER JOIN tblSales S ON P.CompanyID = S.CompanyID
GROUP by C.CompanyID