phpmysqldynamic-queries

Priority based order shortage in self referencing sql query


I am a basic developer not having advanced skills. I have an order processing system in php/mysql and want to calculate the shortages based on priority in SQL. Presently I use php arrays for this but i want to achieve this in database itself.

Here is the queries i have

enter image description here

And this is what i want to achieve

enter image description here

Thanks.


Solution

  • The inner query orders by product and orderid and calculates a running total resetting on product change.

    /*
    drop table orders;
    
    create table orders (orderid varchar(6),productid varchar(10),reqqty int);
    
    create table stock (productid varchar(6), stock int);
    
    truncate table orders;
    insert into orders values
    ('OR250','A45',100),('OR250','A55',200),('OR250','A65',400),
    ('OR260','A45',150),('OR260','A55',25),
    ('OR270','A55',100),('OR270','A65',50),('OR270','B15',500)
    ;
    
    TRUNCATE TABLE STOCK;
    INSERT INTO STOCK VALUES
    ('A45',200),('A55',250),('A65',180),('A75',300),('A85',400),('A95',780),('B15',150),('B25',225);
    */
    
    SELECT  T.ORDERID,T.PRODUCTID,T.REQQTY,T.SHORTAGE,T.STOCKREMAINING AS STOCK
    FROM
    (
    SELECT  @RN:=IF (O.PRODUCTID  <> @PRV,1,@RN+1) RN,
                O.ORDERID,O.PRODUCTID,O.REQQTY
                ,S.PRODUCTID AS SPRODUCTID,S.STOCK
                ,@PRV:=O.PRODUCTID PREVPROD
                ,@RT:=IF(@RT=0 OR @RN = 1,S.STOCK-O.REQQTY,@RT - O.REQQTY)  RT
                ,CASE WHEN @RT < 0 THEN @RT * -1 ELSE 0 END AS SHORTAGE
                ,CASE WHEN @RT >= 0 THEN @RT ELSE 0 END AS STOCKREMAINING
    FROM    (SELECT @RN:=0) RN,(SELECT @PRV:='') PRV,(SELECT @RT:=0) RT,ORDERS O
    JOIN  STOCK  S ON S.PRODUCTID = O.PRODUCTID
    ) T
    ORDER   BY  T.ORDERID,T.PRODUCTID