Write the query to sum the sales and purchase, group by the itemid and open_stock. Then another query to calculate the closing stock. Here is an example
DECLARE @Tbl_Items TABLE (
ItemId INT IDENTITY (1,1),
ItemName VARCHAR(100),
Open_Stock INT
)
DECLARE @tbl_Items_Journals TABLE (
Id INT IDENTITY (1,1),
ItemId INT,
Open_Stock INT,
Purchase INT,
Sales INT,
[Date] DATE
)
INSERT INTO @Tbl_Items
SELECT ' Persil_HS', 100 UNION
SELECT ' Persil_LS', 100 UNION
SELECT ' Persil_GI', 100 UNION
SELECT ' General_7', 100
INSERT INTO @tbl_Items_Journals
SELECT 1, 100, 0, 50, '01/02/2018' UNION
SELECT 1, 100, 25, 0, '01/03/2018' UNION
SELECT 1, 100, 0, 25, '01/03/2018' UNION
SELECT 1, 100, 25, 0, '01/04/2018' UNION
SELECT 2, 100, 0, 10, '01/02/2018' UNION
SELECT 2, 100, 20, 0, '01/03/2018' UNION
SELECT 2, 100, 0, 20, '01/04/2018' UNION
SELECT 3, 100, 0, 70, '01/04/2018' UNION
SELECT 3, 100, 10, 20, '01/04/2018'
;WITH tempSumPurSales AS (
SELECT b.ItemId, b.Open_Stock, ISNULL(SUM(a.Purchase),0) 'Purchase',
ISNULL(SUM(a.Sales),0) 'Sales'
FROM @Tbl_Items b LEFT JOIN @tbl_Items_Journals a ON b.ItemId = a.ItemId
WHERE ((a.[Date] BETWEEN '01/02/2018' and '01/04/2018') OR a.[Date] IS NULL)
GROUP BY b.ItemId, b.Open_Stock
) SELECT *, (Open_Stock + Purchase) - Sales AS Closing_Stock FROM tempSumPurSales
Output:
ItemId Open_Stock Purchase Sales Closing_Stock
1 100 50 75 75
2 100 20 30 90
3 100 10 90 20
4 100 0 0 100