CODEWITHSUNDEEP
sqlsql-server
I Love Stackoverflow
I Love Stackoverflow

Reputation: 6868

Left join returning additional data which I want to exclude

I have 2 tables like below :

Employee: 

EmployeeId
EmployeeName
DeptId

Transactions:

TRID   
EmployeeId
Status(Pending,Done,InProgress,Rejected)

Now I want to get all employees from the EmployeeTable for DeptId = 100. I want to calculate Pending status for those employees whose transactions are pending.

So if employee records are found in Transactions table then just want to return a column saying whether employee has any pending transactions or not)

Query:

SELECT
        e.*,
        CASE WHEN (t.EmployeeId is not null and t.Status!= 'Done')
            THEN CAST(1 AS BIT) 
            ELSE CAST(0 AS BIT) 
        End as IsPendingTransaction,
    FROM 
        Employee e
        left join Transactions t on e.EmployeeId = t.EmployeeId
    where e.DeptId = 100

Now the problem is there are employees who have multiple transactions in transactions table but for those employees, I just wants to consider their latest transaction and calculate IsPendingTransaction status based on that. Basically order by Descending on TRID while calculating IsPendingTransaction status for Employees from transaction table. If employees dont have any transaction then IsPendingTransaction would be "False".

Can anyone please help me with this?

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Use outer apply:

SELECT e.*,
       (CASE WHEN (t.EmployeeId is not null and t.Status <> 'Done')
             THEN CAST(1 AS BIT) 
             ELSE CAST(0 AS BIT) 
        End) as IsPendingTransaction
FROM Employee e OUTER APPLY
     (SELECT TOP (1) t.*
      FROM Transactions t 
      WHERE e.EmployeeId = t.EmployeeId
      ORDER BY t.trID DESC
     ) t
WHERE e.DeptId = 100;

Upvotes: 1

Related Questions