I have three tables that contains the Employee details, their Leave details and their Activity.

Table A

Table B

Table C

What would be the SQL Query that meets the following requirements for the output:

• EACH employee only with the LATEST LeaveDateStart,LeaveDateEnd and Date.
• Exclude "Demoted" from the output column.

For example, the output for above would be:

As you can see above, Gary has multiple records for his leaves and activities, but in the final output we only need the LATEST LeaveStart, LATEST LeaveEnd, LATEST Activity along with the ActivityDATE.

This is the query that I tried but it still returns duplicate record for each user for distinct LeaveType and LatestActivity.

SELECT DISTINCT 
a.EMPLOYEEID, 
a.EMPLOYEENAME, 
a.TITLE, 
MAX(b.LeaveStart) OVER(PARTITION BY a.EMPLOYEEID) as LeaveStart,
MAX(b.LeaveEnd) OVER(PARTITION BY a.EMPLOYEEID) as LeaveEnd,
MAX(c.Date) OVER(PARTITION BY a.EMPLOYEEID) as ActivtyDate,
b.LeaveType,
c.LatestActivity
FROM a
JOIN b
ON a.EMPLOYEEID = b.EMPLOYEEID
JOIN c
ON a.EMPLOYEEID = c.EMPLOYEEID

EDIT: My mistake in the table naming

CodePudding user response：

you can use a query like below where you use row_number to get the latest inside an inner query for each table and NOT exist for filtering out

select 
    A.EmployeeID, A.EmployeeName, A.Title,
    B.LeaveStart, B.LeaveEnd, B.LeaveType,
    C.Date, C.LatestActivity
from TableA A
left join (
    select *, 
    rn = row_number() over (partition by EmployeeID order by LeaveStart desc)
    from TableB 
    )   B
    on B.EmployeeID=A.EmployeeID and B.rn=1
left join (
    select *, 
    rn = row_number() over (partition by EmployeeID order by ActivityDate desc)
    from TableC
    ) C
    on C.EmployeeID=A.EmployeeID and C.rn=1
where not exists 
(select 1 from TableC CC where CC.EmployeeID=A.EmployeeID and CC.LatestActivity='Demoted')

CodePudding user response：

You can use a LATERAL OUTER JOIN query for this (in SQL Server it is called OUTER APPLY). Rough outline of the query:

SELECT *
FROM employee
OUTER APPLY (
    SELECT TOP 1 *
    FROM leaves
    WHERE EmployeeID = employee.EmployeeID
    ORDER BY LeaveStart DESC
) AS oa1
OUTER APPLY (
    SELECT TOP 1 *
    FROM activity
    WHERE EmployeeID = employee.EmployeeID AND LatestActivity <> 'Demoted'
    ORDER BY ActivityDate DESC
) AS oa2