cum sum 1, MySQL - rextester

Language: Layout:

drop table if exists `sales_order1`;
drop table if exists `shipped_order1`;

CREATE TABLE if not exists `sales_order1` (
  `sales_order` varchar(10) NOT NULL,
  `so_create_on` date NOT NULL,
  `sold_to_party` varchar(45) NOT NULL,
  `order_quantity` int(10) unsigned NOT NULL,
  PRIMARY KEY (`sales_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sales_order1` (`sales_order`, `so_create_on`, `sold_to_party`, `order_quantity`) VALUES
('1230503910', '2017-05-07', 'ABC', 9500),
('1261171592', '2016-05-10', 'DEF', 1500);

CREATE TABLE if not exists `shipped_order1` (
  `bill_item` varchar(10) NOT NULL,
  `bill_doc_date` date NOT NULL,
  `bill_qty` int(10) NOT NULL,
  `sales_order` varchar(10) NOT NULL,
  `bill_doc` varchar(45) NOT NULL,
  PRIMARY KEY (`bill_item`,`sales_order`,`bill_doc`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `shipped_order1` (`bill_item`, `bill_doc_date`, `bill_qty`, `sales_order`, `bill_doc`) VALUES
('10', '2016-07-18', 1000, '1230503910', '3230230297'),
('10', '2016-10-19', 2225, '1230503910', '3231642312'),
('10', '2016-07-29', 3775, '1230503910', '3231648901'),
('10', '2016-07-29', 2500, '1230503910', '4433648901'),
('10', '2016-12-05', 1500, '1261171592', '3251725740');

select if (@last_order <> sales_order, @acm_order := order_quantity - bill_qty, @acm_order := @acm_order - bill_qty) as balance,
       sales_order,
       so_create_on,
       order_quantity,
       bill_item,
       bill_qty,
       bill_doc,
       bill_doc_date,
       @last_order := sales_order
from
     (select @last_order := 0, @acm_order := 0) x,
     (SELECT sa.sales_order,
               sa.so_create_on,
               sa.order_quantity,
               sh.bill_item, 
               sh.bill_qty,
               sh.bill_doc, 
               sh.bill_doc_date
        FROM sales_order1 sa
        LEFT JOIN shipped_order1 sh ON sa.sales_order = sh.sales_order
        order by sa.sales_order, sh.bill_doc_date) y
;

drop table `sales_order1`;
drop table `shipped_order1`;


absolute service time: 0,39 sec