Problem
number 1: You have an item table but keep repeating the name of the item in both your Purchase and Issue tables. You need to understand
Database Normalization[
^]
My version of these tables (if I was going to use them - see below) would look more like this (caveat - I'm using table variables in SQL Server because it is all I have to hand at the moment. You may need to tweak the syntax for MySql)
declare @item table (ItemID int, ItemName varchar(30), Qty int, EntryDate date);
declare @Purchase table (ItemID int, PurchaseQTY int , PurchaseDate date);
declare @Issue table(ItemID int, IssueQTY int, IssueDate date);
Problem
number 2: You need to provide more sample data to make this meaningful - the opening stock will be the
previous opening stock plus any purchases for the day minus any issues for the day. You are completely ignoring the previous closing stock and it will be easier to spot things like that if you use slightly more data e.g.
insert into @item (ItemID,ItemName,Qty,EntryDate) values
(1,'bag',10,'2021-07-07'),(2,'box',10,'2021-07-06');
insert into @Purchase(ItemID , ItemName , PurchaseQTY , PurchaseDate) values
(2, 'box' , 10 , '2021-07-06'),(1, 'bag' , 10 , '2021-07-07'),
(2, 'box' , 11 , '2021-07-07'),(1, 'bag' , 20 , '2021-07-07');
insert into @Issue(ItemID , ItemName , IssueQTY , IssueDate) values
(2, 'box' , 3 , '2021-07-06'),(1, 'bag' , 1 , '2021-07-07'),
(2, 'box' , 4 , '2021-07-07'),(1, 'bag' , 2 , '2021-07-07');
Problem
number 3: Your "what I have tried" code uses something called
Stock_view - but you have not told us what that is (and I am not going to try to guess) which makes me suspect you haven't run it
Problem
number 4 You haven't told us what the actual problem is. You should check the posting guidelines for this forum.
Suggestion: Change your schema to simplify it: One table for the items - you would not normally store a Qty on that table either, and one table for the Transactions - both types on the same table. Either use positive quantities for purchases and negative for issues or have a column that indicates whether the stock movement was In or Out E.g.
declare @item table (ItemID int, ItemName varchar(30), EntryDate date);
declare @transaction table (ItemID int, QTY int , TranDate date, Direction char(1));
insert into @item (ItemID,ItemName,Qty,EntryDate) values
(1,'bag','2021-07-07'), (2,'box','2021-07-06');
insert into @transaction(ItemID , QTY , TranDate, Direction) values
(2, 'box' , 10 , '2021-07-06','I'),(1, 'bag' , 10 , '2021-07-07','I'),
(2, 'box' , 11 , '2021-07-07','I'),(1, 'bag' , 20 , '2021-07-07','I'),
(2, 'box' , 3 , '2021-07-06','O'),(1, 'bag' , 1 , '2021-07-07','O'),
(2, 'box' , 4 , '2021-07-07','O'),(1, 'bag' , 2 , '2021-07-07','O');
That way you can calculate the Net stock movements for any single day E.G.
select a.itemid, b.ItemName, TranDate, SUM(case when a.Direction = 'I' then a.QTY ELSE (-1) * a.QTY end) as netDaily
from @transaction a
inner join @item b on a.ItemID = b.ItemID
GROUP BY a.itemid, b.ItemName, Trandate
order by TranDate, a.ItemID
As this is homework I will leave the exercise of working out how to include the previous closing stock (which is the opening stock for the next day) in the calculation. You might want to consider keeping a Stock Table just to record the end of day position - but remember you will need to schedule the update to that daily.
Or you could do that as a first step each time - e.g. using the code above and inserting it into a temporary table.
Or you could calculate it "on the fly" each time - in which case have a look at
MySQL LAG() Function Explained By Practical Examples[
^]
Submit an article or tip