You must Sign In to post a response.
  • Category: SQL Server

    How to Reflecting this Query for a Both Tables.

    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
  • #767584
    IT looks you want to do something like this

    DECLARE @STARTDATE AS DATE
    DECLARE @ENDDATE AS DATE
    SET @STARTDATE='2016-08-18'
    SET @ENDDATE='2016-08-20'
    SELECT T1.Empno, T1.NAME AS empname, T1.Place AS pLACE,
    sUM(Balance) + sUM(CASE WHEN STATUS = 'OK' THEN Receipt ELSE 0 END) -SUM(CASE WHEN STATUS = 'ok' THEN Payment ELSE 0 END) AS ob,
    sUM(Balance) + sUM(CASE WHEN DATE BETWEEN @STARTDATE AND @ENDDATE AND STATUS = 'OK' THEN Receipt ELSE 0 END)
    -sUM(CASE WHEN DATE BETWEEN @STARTDATE AND @ENDDATE AND STATUS = 'OK' THEN Payment ELSE 0 END) AS cb
    FROM TableOne T1
    lEFT JOIN TableTwo TW ON T1.Empno = TW.Empno


    GROUP BY t1.Empno, t1.Name, t1.Place

    Many Thanks
    Tejinder Singh Barnala
    /*I have the simplest tastes. I am always satisfied with the best*/


Sign In to post your comments