How to Solve this Query in Mysql -- (Bandi and Ranjari) - Most Urgent

Posted by Jayakumars under Sql Server on 8/5/2016 | Points: 10 | Views : 220 | Status : [Member] [MVP] | Replies : 0
I have master and Detail Table here how to match sum and total value i metion below my output.

CREATE  TABLE `test`.`mastable` (

`Id` INT NOT NULL AUTO_INCREMENT ,

`EmpNo` INT NULL ,

`Name` VARCHAR(45) NULL ,

`Balance` DECIMAL(6,2) NULL ,

PRIMARY KEY (`Id`) );


CREATE TABLE `test`.`transtable` (

`Id` INT NOT NULL AUTO_INCREMENT ,

`EmpNo` INT NULL ,

`Date` DATE NULL ,

`Receipt` DECIMAL(6,2) NULL ,

`Payment` DECIMAL(6,2) NULL ,

PRIMARY KEY (`Id`) );


INSERT INTO `test`.`mastable` (`EmpNo`, `Name`, `Balance`) VALUES (10, 'David', '1000');

INSERT INTO `test`.`mastable` (`EmpNo`, `Name`, `Balance`) VALUES (20, 'Miller', '300');

INSERT INTO `test`.`mastable` (`EmpNo`, `Name`, `Balance`) VALUES (30, 'Warner', '0');




INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-04-05', '100');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-05-07', '200');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-06-08', '100');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-07-10', '200');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-08-11', '200');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Payment`) VALUES (10, '2015-09-12', '500');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-10-12', '300');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-10-14', '500');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-11-15', '300');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-12-17', '200');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2016-01-18', '100');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2016-02-10', '500');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Payment`) VALUES (10, '2016-02-19', '300');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2016-03-22', '300');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2016-03-31', '500');


INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (20, '2016-04-22', '1000');




I need Output this
===================
Shareno Name OB Recept Payment SumRecept OB sumpaymt Temp CB
10 David 1000 2700 300 800 1800 500 1300 4200
20 Miller calculate forumla above record

Mark as Answer if its helpful to you


Responses

(No response found.)

Login to post response