Opening Stock, TotalStockIn, TotalStockOut, ClosingStock By Date sql Query

[Priya22]

I have two tables with primary key and foreign key (ItemId)

Table 1 (Items)

ItemId   ItemName   OPStock  OPStockValue
1         Pen         100      1000
2         Pencil      50       5000

Table 2 (Inventory)

TransactionType VchNo   ItemID  ItemName                Qty   TaxableAmt    EntryDate
Sale            2       1       KFL UREA NEEM COTED     70      16940       16-04-2020
Sale            1       5       IPL MOP                 60      49200       16-04-2020
Sale            3       2       IPL DAP (IMPORTED)      60      58800       16-04-2020
Sale            4       2       IPL DAP (IMPORTED)      10      9800        16-04-2020
Sale            4       5       IPL MOP                 50      41000       16-04-2020
Sale            5       2       IPL DAP (IMPORTED)      10      9800        23-04-2020 
Purchase        1      67       PADDY SEEDS DAYAL GOLD  30      201000      23-04-2020
Purchase        1      97       PADDY SEEDS DAYAL GOLD  15      100500      23-04-2020
Sale            8       2       IPL DAP (IMPORTED)      10      9800        24-04-2020
Purchase        2       1       KFL UREA NEEM COTED     1600    381008.16   24-04-2020
Purchase        3       7       CASTER OIL CAKE ORGANIC 500     261250      24-04-2020
Purchase        4      38       PADDY SEEDS MANSURI     175     682500      02-05-2020
Sale           19      39       PADDY SEEDS MTU-1001    5       20750       02-05-2020
Sale           19      40       PADDY SEEDS MTU7029     1.25    4812.5      03-05-2020

Now When I Pass @FromDate and @Todate I want to output like below:

EntryDate ItemName OpQty PurchQty SaleQty ClosingBal

database link

What I have tried:

I am trying to solve using Left Join But the result is not correct

SELECT I.ItemID, I.ItemName, I.OPStock AS OpStock, I.OPStockValue AS OpValue, SUM(SP.Qty) AS InQty, SUM(SP.TaxableAmt) AS InValue, SUM(SS.Qty) AS OutQty, SUM(SS.TaxableAmt) AS OutValue 
FROM items AS I
LEFT JOIN 
inventory AS SP 
ON SP.ItemID = I.ItemID 
LEFT JOIN
inventory AS SS
ON I.ItemID = SS.ItemID 
WHERE SP.TransactionType = 'Purchase' AND SS.TransactionType = 'Sale' 
GROUP BY I.ItemID 
ORDER BY I.ItemName ASC

 

Edited July 8, 2021 by Priya22

[niche]

How is it not correct?