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 |
|
p.shaw3@ukonline.co.uk
Posting Yak Master
103 Posts |
 Posted - 2006-09-07 : 04:27:15
|
| I am using:SELECT (CASE WHEN LM.dbo.TimeSheets.ActivityCode = 'Leaver' THEN '1' ELSE '0' end) AS 'Leaver'which grabs all users in the table between 2 dates using a BETWEEN clause. The CASE returns a user twice (once with a zero and once with a 1) if they have had 'Leaver' as their Activity Code at any day in the BETWEEN clause. It always returns a zero as a user has to have an activity code for every day. This is not really what I want.I actually need to eliminate any users from selection that show as a 'Leaver' at any point in the BETWEEN clause, but I cannot figure out how to do this. Can anyone please help. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-09-07 : 04:31:35
|
| I am not clear what you want. Please provide some example data, table structures & expected result.Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-07 : 04:32:33
|
| Use MAX function and GROUP BY the user id.Peter LarssonHelsingborg, Sweden |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-09-07 : 04:34:03
|
| [code]SELECT Sum(CASE WHEN LM.dbo.TimeSheets.ActivityCode = 'Leaver' THEN 1 ELSE 0 end) AS 'Leaver' [/code]Chirag |
|
|
|
p.shaw3@ukonline.co.uk
Posting Yak Master
103 Posts |
Posted - 2006-09-07 : 07:38:20
|
| Thanks Guys, but these still give me 2 rows where the person has had 'Leaver'. I want to eliminate a user that appears twice. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-07 : 07:40:46
|
| [code]SELECT UserID AS [Leaver]FROM LM.dbo.TimeSheetsWHERE ActivityCode = 'Leaver'group by UserID -- see my previous posthaving COUNT(*) > 0 -- Or 1. Try yourself with the right value[/code]Peter LarssonHelsingborg, Sweden |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-09-07 : 07:45:09
|
quote:
Originally posted by p.shaw3@ukonline.co.uk
Thanks Guys, but these still give me 2 rows where the person has had 'Leaver'. I want to eliminate a user that appears twice.
Do you want to eliminate the same user appearing twice or you want only one user having 'Leaver'?Chirag |
|
|
|
p.shaw3@ukonline.co.uk
Posting Yak Master
103 Posts |
Posted - 2006-09-07 : 08:28:51
|
| I want to eliminate ALL users who appear twice. |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-09-09 : 01:54:25
|
| Generally this is done.. Select * From <LM.dbo.TimeSheets> Tbl1 Where PK = (Select Max(PK) From <LM.dbo.TimeSheets> Tbl2 Where Tbl1.User = Tbl2.User)Replace PK with the primary key of that table..Chirag |
|
|
|
|
|
|
Eliminate users on condition.