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
GROUP BY
anything so your sum is entirely correct in the eyes of MySQL (on many others you'd get an error) – Sami Kuhmonen