6055 6055 - 3 months ago 17
SQL Question

Get details of employee whose salary came after his release date

I have 2 tables -

Employee
and
Salary
.

Employee has 3 columns :
EmpId , JoiningDate and LastDate.


Salary table has 3 columns :
EmpId, SalaryPerMonth, SalaryDate.


select * from salG;

1 02-FEB-2016 100
2 31-JAN-2016 200
2 28-FEB-2016 200
2 01-APR-2016 200
3 30-JAN-2016 300
3 23-FEB-2016 300
3 29-MAR-2016 300

select * from empG;

1 01-JAN-2016 31-JAN-2016
2 10-JAN-2016 31-MAR-2016
3 11-JAN-2016 31-MAR-2016


I need to get empId and SalaryPerMonth of those employees whose salary came after their lastDate.

I tried -

select emp.id,s.salary
from empG emp,
salG s
where emp.END_DATE < ( select max(s.sal_date) from salG s where emp.id= s.id);


But this is a cartesian product. Is there a better way?

Answer

You haven't provided sample expected output but from what I understand this should meet your needs just fine. No need for subqueries or any of that, you can do the logic in the join;

Sample table 'Employee'

IF OBJECT_ID('tempdb..#Employee') IS NOT NULL DROP TABLE #Employee
CREATE TABLE #Employee (EmpID int, JoiningDate date, LastDate date)
INSERT INTO #Employee
VALUES
(1,'2016-01-01','2016-01-31')
,(2, '2016-01-10','2016-03-31')
,(3, '2016-01-11','2016-03-31')

Sample Table 'Salary'

IF OBJECT_ID('tempdb..#Salary') IS NOT NULL DROP TABLE #Salary
CREATE TABLE #Salary (EmpId int, SalaryPerMonth int, SalaryDate date)
INSERT INTO #Salary
VALUES
 (1, 100, '2016-02-02')
,(2, 200, '2016-01-31')
,(2, 200, '2016-02-28')
,(2, 200, '2016-04-01')
,(3, 300, '2016-01-30')
,(3, 300, '2016-02-23')
,(3, 300, '2016-03-29')

Query

SELECT
    e.EmpID
    ,e.JoiningDate
    ,e.LastDate
    ,s.SalaryDate
    ,s.SalaryPerMonth
FROM #Employee e
INNER JOIN #Salary s
    ON e.EmpID = s.EmpId
    AND e.LastDate < s.SalaryDate

Output

EmpID   JoiningDate LastDate    SalaryDate  SalaryPerMonth
1       2016-01-01  2016-01-31  2016-02-02  100
2       2016-01-10  2016-03-31  2016-04-01  200