1

I am trying to show total of unsold products using mysql and I want to calculate it using FIFO manner

For example

Purchase

stockdate   stockquantity 
24/02          100
25/02          200

Sales

salesdate   salesquantity
25/02          50

Output

Items unsold = 250

But Items should get sold from date 24/02

Following is the procedure what I tried so far

BEGIN

DECLARE item_sold INT;
DECLARE total_quantity INT;

SELECT item_sold= (SELECT sum(salesquantity) from Sales);

SELECT total_quantity = (SELECT stockquantity , case when stockquantity < item_sold then item_sold = item_sold-stockquantity when stockquantity > item_sold then stockquantity= stockquantity-item_sold AND item_sold = 0 AND +total_quantity when stockquantity = item_sold then item_sold = 0 end as total_quantity from Purchase order by stockdate);

select item_sold;
select total_quantity;
END

Can any one please suggest me what I am doing wrong? or is this correct way of doing this?

Priyanka Pawar
  • 205
  • 1
  • 4
  • 16
  • 3
    Possible duplicate of [Create a Cumulative Sum Column in MySQL](http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql) – Shadow Feb 25 '16 at 10:29
  • Thank you @Shadow but I have a condition to subtract also – Priyanka Pawar Feb 25 '16 at 10:44
  • So what? You include it with a negative sign in the addition process. Sg like `@csum := @csum + stockquantity - salesquantity`. – Shadow Feb 25 '16 at 11:02

0 Answers0