phpmysqlcodeigniterquery-builder

Incorrect Opening Balance return for MySQL query


I have a view as follows to filter items for receiving & issuing like A4, A3, .......within the selected date range from Start Date to End Date. enter image description here

receiving are denoted by "purchase" and issuing are denoted as "issue" in the table under "order_status". As this example, suggest, there are only issues in the date range 2023-08-01 to 2023-08-31. Received Qty in the form outs by the following code

(CASE store_update_stock.order_status
  WHEN "ob" AND "purchase" AND billed_date <= "$start" 
  THEN store_update_stock_details.qty 
  ELSE 0
  END) p2,  

Desired output

I want to get the end balance as at 31st Jul 2023, as the opening balance as at 01st Aug 2023 like as 255 for A4. Then the issues can be deducted from this opening balance.

Error

But the filter outs '0' for the start date while the issues are outs correctly.

My Model as follows :

public function issueDetailReport($id,$start,$end){
    $this->db->select('*,     
      (CASE 
      WHEN store_update_stock.order_status = "issue"  THEN store_branch.branch_name     
      ELSE tbl_supplier.supplier_name
      END) supplier_officer_name,

      (CASE 
      WHEN store_update_stock.order_status = "issue" THEN store_update_stock.request_no      
      ELSE store_update_stock.bill_no
      END) number,      

      (CASE store_update_stock.order_status
      WHEN "issue" AND store_update_stock.billed_date <= "$start"  
      THEN store_update_stock_details.qty * (-1)    
      ELSE store_update_stock_details.qty * (-1)
      END) quantity,

      (CASE store_update_stock.order_status
      WHEN "purchase" AND billed_date <= "$start" 
      THEN store_update_stock_details.qty 
      ELSE 0
      END) p2,     

      (CASE store_update_stock.order_status      
      WHEN "purchase" THEN store_update_stock_details.qty AND store_update_stock.billed_date <= "$start"  
      WHEN "issue" THEN store_update_stock_details.qty AND store_update_stock.billed_date <= "$start" 
      END) balance    
     ');    

    $this->db->from('store_update_stock');
    $this->db->join('store_update_stock_details','store_update_stock.update_stock_id=store_update_stock_details.update_stock_id', 'inner');     
    $this->db->join('store_branch','store_update_stock.branch_id=store_branch.branch_id', 'left' );     
    $this->db->join('tbl_supplier','store_update_stock.supplier=tbl_supplier.supplier_id', 'left');     
    $this->db->join('store_item','store_update_stock_details.item=store_item.item_id', 'left');
    $this->db->where("store_update_stock.status='1' and store_item.item_id=$id");
    $this->db->where("store_update_stock_details.qty <> 0");
    if($start!=NULL && $end!=NULL)
        $this->db->where("store_update_stock.billed_date BETWEEN '$start' AND '$end'");     
    $this->db->order_by('store_update_stock.billed_date','ASC');
    $q=$this->db->get();
    if($q->num_rows()>0){
        return $q->result();
    }
    return false;
}   

What is going wrong. Can anyone help ?


Solution

  • With MySQL v8, you can use window functions like SUM() OVER () AND LAG() to achieve a result something like below:

    ORDER_status billed_date Previous_balance qty Qty_balance
    received 2023-07-15 NULL 100 100
    issued 2023-07-16 100 5 95
    issued 2023-07-17 95 10 85
    issued 2023-07-18 85 15 70
    issued 2023-07-19 70 11 59
    issued 2023-07-21 59 3 56
    issued 2023-07-23 56 9 47
    received 2023-07-25 47 100 147
    issued 2023-07-26 147 20 127
    issued 2023-07-27 127 19 108
    issued 2023-07-28 108 8 100
    issued 2023-07-30 100 7 93
    issued 2023-07-31 93 9 84
    issued 2023-08-01 84 5 79
    issued 2023-08-02 79 9 70
    issued 2023-08-03 70 6 64
    issued 2023-08-04 64 7 57
    issued 2023-08-05 57 11 46
    issued 2023-08-06 46 21 25

    This is the query:

    WITH cte AS
    (SELECT order_status,
            billed_date,
            qty,
           SUM(IF(order_status='received',qty,0)-IF(order_status='issued',qty,0))
            OVER (ORDER BY billed_date) AS Qty_balance
       FROM store_update_stock
      ORDER BY billed_date) 
    SELECT order_status,
            billed_date,
            LAG(Qty_balance) OVER (ORDER BY billed_date) Previous_balance,
            qty,
            Qty_balance
       FROM cte;
    

    .. or if you're more familiar with derived table, just position the SELECT .. above the query in bracket then assign it with an alias.

    SELECT order_status,
            billed_date,
            LAG(Qty_balance) OVER (ORDER BY billed_date) Previous_balance,
            qty,
            Qty_balance
       FROM (SELECT order_status,
            billed_date,
            qty,
           SUM(IF(order_status='received',qty,0)-IF(order_status='issued',qty,0))
            OVER (ORDER BY billed_date) AS Qty_balance
       FROM store_update_stock
      ORDER BY billed_date) a;
    

    Here's a fiddle: https://dbfiddle.uk/-vWlsi8w