declare @AccountsOpen table(EmployeeID int, AccountID int)
declare @AccountsClosed table(EmployeeID int, AccountID int)
insert into @AccountsOpen values(1, 101),(2,201),(3,301),(4,401)
insert into @AccountsClosed values(1, 101),(1,201),(3,401)
Here Two accounts(101,201) colsed by employee1
One account(401) is closed by employee3
301 is not yet closed so employee3 gets another point
As per my understanding, result should be as follows:
employeeid TotalPoints
1 2
3 2
SELECT employeeid, count(points) 'totalPoints'
FROM (SELECT employeeid, 1 points
FROM @AccountsOpen
WHERE AccountID NOT IN (select AccountID from @AccountsClosed)
UNION all
select EmployeeID, 1 Points
from @AccountsClosed
) t
GROUP BY employeeid
--
Chandu