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.
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 accountAccountsOpen EmployeeID (int) AccountID (int) AccountsClosed EmployeeID (int) AccountD (int)If an employee closes an account they get 1 pointIf 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? |
|
|
regex
Starting Member
2 Posts |
Posted - 2012-11-19 : 22:53:29
|
How do I set this up? |
|
|
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 ) tGROUP BY employeeid [/code]--Chandu |
|
|
|
|
|
|
|