How to subtract inventory and sale using sqlserver

Question

Here is the recursive table format:

Example:

Suppose I have Input stock number is 500 and Quantity(sale quantity) is 5 then remaining stock is 495 , In the next case when i sell some other item in Input stock must be 495 instead of 500 and if quantity is 6 then 489..

how to calculate using sqlserver?? here is the stored procedure:

select i.itemid,i.ItemName,i.EntryDate as 'Purchase Date',i.PurchaseRate as 'Purchase Amount',
s.SaleDate,isnull(s.TotalPrice,0)as 'SalesAmount',sum(i.quantity)as 'Input Stock',s.Quantity,
sum(i.quantity)-sum(ISNULL(s.quantity,0)) as 'Remaining Stock'
from Inv_Medicine i
left join Inv_Sales s on s.itemid=i.itemid
group by i.itemid,i.ItemName,i.EntryDate,i.PurchaseRate,s.SaleDate,s.TotalPrice,s.Quantity

The above query not working..any suggestion???

How to subtract inventory and sale ??

i want instead of 500 must be 495 and after 495 if quantity is 6 remaining stock 489..depends upon the data(input stock and quantity data dynamically)

Nilesh Shah · Answer

First of all your table design seems to be wrong you should have a separate master table to store item wise inventory so that there is only one place to store and get latest inventory which can be locked for updates too. Anyways I will concentrate my answer to your design. I see you are missing Order by SaleDate Desc statement which will give you the latest Remaining Stock column value. e.g. you want the value 495 here which can be selected by ordering in descending manner by last sale date.