How to Reflecting this Query for a Both Tables.

Posted by Jayakumars under Sql Server on 8/17/2016 | Points: 10 | Views : 170 | Status : [Member] [MVP] | Replies : 0
Hi

My 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


Mark as Answer if its helpful to you


Responses

(No response found.)

Login to post response