Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Flag on Create SQL?

Author  Topic 

regex
Starting Member

2 Posts

Posted - 2012-11-19 : 19:01:18
There are two tables, AccountsOpen and AccountsClosed, which keeps track of which employee has openedand closedeach account
AccountsOpen
EmployeeID (int)
AccountID (int)

AccountsClosed
EmployeeID (int)
AccountD (int)

If an employee closes an account they get 1 point
If no one closes an account, the employee that opens the account gets 1 point

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-19 : 20:06:25
so What is the question?
Go to Top of Page

regex
Starting Member

2 Posts

Posted - 2012-11-19 : 22:53:29
How do I set this up?
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-20 : 02:22:13
[code]
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
[/code]

--
Chandu
Go to Top of Page
   

- Advertisement -