How to Reflecting this Query for a Both Tables.
HiMy Table Structure with Data like this
CREATE TABLE `test`.`TableOne` (
`Id` INT NOT NULL AUTO_INCREMENT ,
`Empno` INT NULL ,
`Name` VARCHAR(45) NULL ,
`Balance` DECIMAL(6,2) NULL ,
`Place` VARCHAR(45) NULL ,
PRIMARY KEY (`Id`) );
CREATE TABLE `test`.`TableTwo` (
`Id` INT NOT NULL AUTO_INCREMENT ,
`Date` DATE NULL ,
`Empno` INT NULL ,
`Receipt` DECIMAL(6,2) NULL ,
`Payment` DECIMAL(6,2) NULL ,
`Status` VARCHAR(45) NULL ,
PRIMARY KEY (`Id`) );
INSERT INTO `test`.`tableone` (`Empno`, `Name`, `Balance`, `Place`) VALUES (100, 'John', '1500', 'Wasinton DC');
INSERT INTO `test`.`tableone` (`Empno`, `Name`, `Balance`, `Place`) VALUES (101, 'Joselin', '1000', 'Dexcity');
INSERT INTO `test`.`tableone` (`Empno`, `Name`, `Balance`, `Place`) VALUES (102, 'Rusfal', '0', 'Donxes');
INSERT INTO `test`.`tableone` (`Empno`, `Name`, `Balance`, `Place`) VALUES (103, 'Raser', '100', 'versity');
INSERT INTO `test`.`tableone` (`Empno`, `Name`, `Balance`, `Place`) VALUES (104, 'rse', '2500', 'sew');
INSERT INTO `test`.`TableTwo` (`Date`, `Empno`, `Receipt`, `Payment`, `Status`) VALUES ('2016-08-15', 100, '1000', '0', 'OK');
INSERT INTO `test`.`TableTwo` (`Date`, `Empno`, `Receipt`, `Payment`, `Status`) VALUES ('2016-08-15', 100, '0', '1000', 'OK');
INSERT INTO `test`.`TableTwo` (`Date`, `Empno`, `Receipt`, `Payment`, `Status`) VALUES ('2016-08-17', 101, '0', '2000', 'OK');
INSERT INTO `test`.`TableTwo` (`Date`, `Empno`, `Receipt`, `Payment`, `Status`) VALUES ('2016-08-18', 103, '100', '0', 'NOT OK');
INSERT INTO `test`.`TableTwo` (`Date`, `Empno`, `Receipt`, `Payment`, `Status`) VALUES ('2016-08-19', 100, '1500', '0', 'OK');
INSERT INTO `test`.`TableTwo` (`Date`, `Empno`, `Receipt`, `Payment`, `Status`) VALUES ('2016-08-20', 100, '0', '1000', 'OK');
Then I need Output this
starting date '2016-08-18' ending date '2016-08-20'
Empno Name Place OB CB
100 John Wasinton DC 2400 2000
101 Joselin Dexcity 1000 0
102 Rusfal Donxes 0 0
etc
103
104
also
Condition
OB less amount get less than starting Date based on status OK Only
forumula
OB=Balance+Receipt-Payment
Condition
CB calculation from date to date based on status OK Only
forumula
CB=Balance+Receipt-Payment