Manoj Maharana

Manoj Maharana

  • NA
  • 362
  • 126.7k

How to subtract inventory and sale using sqlserver

Jan 25 2017 6:28 AM
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:

  1. select i.itemid,i.ItemName,i.EntryDate as 'Purchase Date',i.PurchaseRate as 'Purchase Amount',  
  2.  s.SaleDate,isnull(s.TotalPrice,0)as 'SalesAmount',sum(i.quantity)as 'Input Stock',s.Quantity,  
  3.  sum(i.quantity)-sum(ISNULL(s.quantity,0)) as 'Remaining Stock'  
  4.  from Inv_Medicine i  
  5.  left join Inv_Sales s on s.itemid=i.itemid  
  6.  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)

 

Answers (1)