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
