how to solve this Query Issue

Posted by Jayakumars under Sql Server on 8/5/2016 | Points: 10 | Views : 244 | Status : [Member] [MVP] | Replies : 1
Hi


I have table1 and table2 both table relationship
Empno. here i need when i entered Date value like this
'2015-07-07' i need shows following field

Table1 : Empno,name,balance
Table2: sum receipt lessthan date this '2015-07-07'
all memeber sum receipt separate column and payment separate column
shows

i need date based less then data sum need

I mention table with data given below.



CREATE  TABLE `test`.`Table1` (

`id` INT NOT NULL AUTO_INCREMENT ,

`empno` INT NULL ,

`name` VARCHAR(45) NULL ,

`Balance` DECIMAL(18,2) NULL ,

PRIMARY KEY (`id`) );




CREATE TABLE `test`.`Table2` (

`id` INT NOT NULL AUTO_INCREMENT ,

`Date` DATE NULL ,

`Empno` INT NULL ,

`Receipt` DECIMAL(6,2) NULL ,

`Payment` DECIMAL(6,2) NULL ,

PRIMARY KEY (`id`) );




INSERT INTO `test`.`Table1` (`empno`, `name`, `Balance`) VALUES (100, 'Andrew', '1500');

INSERT INTO `test`.`Table1` (`empno`, `name`, `Balance`) VALUES (200, 'Pawan', '2500');

INSERT INTO `test`.`Table1` (`empno`, `name`, `Balance`) VALUES (300, 'David', '8500');

INSERT INTO `test`.`Table1` (`empno`, `name`, `Balance`) VALUES (400, 'Radan', '9500');

INSERT INTO `test`.`Table1` (`empno`, `name`, `Balance`) VALUES (500, 'Wesley', '12500');




INSERT INTO `test`.`Table2` (`Date`, `Empno`, `Receipt`, `Payment`) VALUES ('2015-01-01', 100, '500', '0');

INSERT INTO `test`.`Table2` (`Date`, `Empno`, `Receipt`) VALUES ('2015-03-03', 100, '300');

INSERT INTO `test`.`Table2` (`Date`, `Empno`, `Receipt`, `Payment`) VALUES ('2015-04-04', 100, '0', '300');

INSERT INTO `test`.`Table2` (`Date`, `Empno`, `Receipt`, `Payment`) VALUES ('2015-05-05', 100, '400', '0');

INSERT INTO `test`.`Table2` (`Date`, `Empno`, `Receipt`, `Payment`) VALUES ('2015-07-07', 200, '200', '0');

INSERT INTO `test`.`Table2` (`Date`, `Empno`, `Receipt`, `Payment`) VALUES ('2015-08-08', 300, '400', '0');

INSERT INTO `test`.`Table2` (`Date`, `Empno`, `Payment`) VALUES ('2015-09-09', 400, '750');
INSERT INTO `test`.`Table2` (`Date`, `Empno`, `Receipt`, `Payment`) VALUES ('2015-10-10', 100, '450', '0');


Mark as Answer if its helpful to you


Responses

Posted by: Joginder on: 8/6/2016 [Member] Starter | Points: 25

Up
0
Down
please make a sample output for better understand.


Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response