SELECT c1.date, SUM(IFNULL(cx.purchase - cx.sold, 0)) + MAX(p.openqty) AS opening, c1.purchase, c1.sold,SUM(IFNULL(cx.purchase - cx.sold, 0)) + MAX(p.openqty) + c1.purchase - c1.sold AS closing FROM ( SELECT open_qt ASopenqty FROM mixed ) p LEFT JOIN ( SELECT a.date, a.qty AS purchase, b.qty AS sold FROM arrival a, pouring b WHEREa.date = b.date ORDER BY a.date ) c1 ON c1.purchase + p.openqty > 0 LEFT JOIN ( SELECT a.date, a.qty AS purchase, b.qtyAS sold FROM arrival a, pouring b WHERE a.date = b.date ORDER BY a.date ) cx ON c1.date > cx.date GROUP BY c1.date,c1.purchase, c1.sold
That would be because I hadn’t had enough coffee when I first looked at it and misread the title as jQuery. So after “helpfully” moving it to JS, I’ve now moved it back to PHP.
It might be worth clarifying whether you need help with getting the results you want from the database, or whether you want help in getting those results presented in a web page - one is a database question, the other ‘may’ be a PHP question, but it’s currently difficult to tell.