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.
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,
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.
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 ?
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