How to Filter values in two tables using inner join? How to filter values in two tables using inner join. 

There are two tables Employee and SalaryIncrement:


  


Sample data: 

CREATE TABLE Employee
(
EmployeeId INT IDENTITY(1,1) NOT NULL,
FullName VARCHAR(255) NOT NULL,
MonthlySalary MONEY NOT NULL
PRIMARY KEY(EmployeeId)
)

INSERT INTO Employee (FullName, MonthlySalary) VALUES ('John', 12000.00)
INSERT INTO Employee (FullName, MonthlySalary) VALUES ('Lee', 20000.00)


CREATE TABLE SalaryIncrement
(
SalaryIncrementId INT IDENTITY(1,1) NOT NULL,
SalaryIncreaseFrom DATE NOT NULL,
AmountOfSalaryIncrease MONEY NOT NULL,
EmployeeId INT NOT NULL
PRIMARY KEY(SalaryIncrementId)
)

INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2012-02-05', 5000.00, 2)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2012-02-05', 1000.00, 1)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2013-05-05', 2000.00, 1)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2013-04-05', 1000.00, 2)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2014-08-08', 1500.00, 1)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2016-03-01', 500.00, 1)


From the above two tables I need to retrieve data like in this image below.

I also Need to display additional two columns Old Monthly Salary and New Monthly Salary calculated by Amount of Salary increased.

Result:
The first thing to do is a self JOIN on SalaryIncrement. This will be used to calculate the SUM of SalaryIncrease.

Query:

SELECT s1.SalaryIncrementId, s1.EmployeeId, s1.SalaryIncreaseFrom, s1.AmountOfSalaryIncrease, SUM(s2.AmountOfSalaryIncrease) as OldSalary
FROM @SalaryIncrement s1
INNER JOIN @SalaryIncrement s2 
    ON s1.EmployeeId = s2.EmployeeId AND s1.SalaryIncreaseFrom &lt;= s2.SalaryIncreaseFrom
GROUP BY s1.SalaryIncrementId, s1.EmployeeId, s1.SalaryIncreaseFrom, s1.AmountOfSalaryIncrease


Then this query only needs to be JOIN to Employee and it can do the math between the SUM and the current MonthlySalary (sample link).

Query:

SELECT sal.SalaryIncrementId, sal.SalaryIncreaseFrom, emp.MonthlySalary-sal.OldSalary, sal.AmountOfSalaryIncrease,  emp.MonthlySalary-sal.OldSalary + sal.AmountOfSalaryIncrease
FROM Employee emp
INNER JOIN (
    SELECT s1.SalaryIncrementId, s1.EmployeeId, s1.SalaryIncreaseFrom, s1.AmountOfSalaryIncrease, SUM(s2.AmountOfSalaryIncrease) as OldSalary
    FROM SalaryIncrement s1
    INNER JOIN SalaryIncrement s2 
        ON s1.EmployeeId = s2.EmployeeId AND s1.SalaryIncreaseFrom &lt;= s2.SalaryIncreaseFrom
    GROUP BY s1.SalaryIncrementId, s1.EmployeeId, s1.SalaryIncreaseFrom, s1.AmountOfSalaryIncrease
) as sal
    ON sal.EmployeeId = emp.EmployeeId
ORDER BY sal.EmployeeId, sal.SalaryIncreaseFrom


Output:

SalaryIncrementId   SalaryIncreaseFrom  OldMonthlySalary    AmountOfSalaryIncrease  NewMonthlySalary
2                   2012-02-05          7000,00             1000,00                 8000,00
3                   2013-05-05          8000,00             2000,00                 10000,00
5                   2014-08-08          10000,00            1500,00                 11500,00
6                   2016-03-01          11500,00            500,00                  12000,00
1                   2012-02-05          14000,00            5000,00                 19000,00
4                   2013-04-05          19000,00            1000,00                 20000,00