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)

here is the Inv_Medicine and Inv_Sale table format and table: Inv_Medicine:

Inv_Medicine structure:

Inv_Sale:

Inv_Sale structure:

Answer 1

manoj ,

DECLARE @date1 datetime='2017-01-25 04:48:03.830'
DECLARE @date2 datetime='2017-01-26 04:48:03.830'

 SELECT t1.*, CurrentStock = (isnull(t1.Quantity,0)-
  SUM(isnull(t2.salsesquantity,0)))+isnull(t1.salsesquantity,0),
  isnull(t1.salsesquantity,0)
  as SalesProductQunatity, RemainingStock = isnull(t1.Quantity,0)-
  SUM(isnull(t2.salsesquantity,0)) 

   FROM ( SELECT
  im.ItemName,im.PurchaseRate,Ins.SalesDate,ins.totalprice,ins.quantity
  as salsesquantity,im.Quantity , ROW_NUMBER()OVER( partition by
  Ins.itemId ORDER by ins.SalesDate) as ranknumbr  FROM 
  [dbo].[Inv_Medicine] IM LEFT JOIN [dbo].Inv_Sales Ins on
 IM.ItemId=Ins.ItemId ) t1

  INNER JOIN 
  ( SELECT
  im.ItemName,im.PurchaseRate,Ins.SalesDate,ins.totalprice,ins.quantity
 as salsesquantity,im.Quantity , ROW_NUMBER()OVER( partition by
 Ins.itemId ORDER by ins.SalesDate) as ranknumbr  FROM 
  [dbo].[Inv_Medicine] IM LEFT JOIN [dbo].Inv_Sales Ins on
  IM.ItemId=Ins.ItemId  ) as t2  



 ON t1.ranknumbr >= t2.ranknumbr AND t1.ItemName=t2.ItemName 
 WHERE t1.SalesDate BETWEEN @date1 AND @date2
 GROUP BY
  t1.ranknumbr,
 t1.SalesDate,t1.ItemName,t1.PurchaseRate,t1.totalprice,t1.salsesquantity,
 t1.Quantity,t2.    Quantity

   ORDER by t1.ItemName DESC

Please check my answer , It will be help you . Let me know if you stuck any where .