How to satisfy 2 different Requirement in a single query

Posted by Jayakumars under Sql Server on 8/5/2016 | Points: 10 | Views : 261 | Status : [Member] [MVP] | Replies : 0
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');



output Condition

columns 
Empno,name,Balance, Date,Recepit,payment,Total


1.When i enter from date to date
less than fromdate i need calculate sum(receipt) separate column,
sum(payment) separate column before from date
Total should be Balance+Receipt-Payment before from date.

2.empno,name,Balance should be based on fromdate and todate


i need shows all condition satisfied in single query how will do this

Mark as Answer if its helpful to you


Responses

(No response found.)

Login to post response