SELECT O.Codes as [Code], O.Products, ; OpeningQty as [OpeningQty], ; PurchaseQty as [PurchaseQty], ; PurchaseAmount as [PurchaseAmount], ; OpeningQty + NVL(PurchaseQty,0) as [TotalQty], ; OpeningAmount + NVL(PurchaseAmount,0) as [TotalAmount], ; NVL(SoldQty,0) as [SoldQty], ; NVL(SoldAmount,0) as [SoldAmount], ; OpeningQty + NVL(PurchaseQty,0) - NVL(SoldQty,0) as [ClosingQty], ; (OpeningQty + NVL(PurchaseQty,0) - NVL(SoldQty,0)) * IIF(LastStocked > NVL(LastPurchased,{}),LastStockRate, LastPurchaseRate) as [ClosingAmount] ; FROM (select Codes, Products, SUM(qty) as OpeningQty, ; SUM(Amount) as OpeningAmount, MAX(dates) as LastStocked from Open_Stock group by Codes, Products) O ; INNER JOIN (select Rate as LastStockRate, Codes, Dates FROM Open_Stock) O1 ON O.Codes = O1.Codes AND O.LastStocked = O1.Dates ; LEFT JOIN (select Codes, Products, ; SUM(qty) as PurchaseQty, SUM(Amount) as PurchaseAmount, MAX(Dates) as LastPurchased ; from Purchase group by Codes, Products) P ; ON O.Codes = P.Codes ; LEFT JOIN (select Rate as LastPurchaseRate, Codes, Dates FROM Purchase) P1 on P.Codes = P1.Codes and P.LastPurchased = P1.Dates ; LEFT JOIN (select Codes, Products, ; SUM(qty) as SoldQty, SUM(Amount) as SoldAmount ; from Sales group by Codes, Products) S ; ON O.Codes = S.Codes