Query question - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Query question

Author

Topic

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-22 : 08:47:52

Hi to all,

I have a problem that I must get round and can't think how I can do it. I have a table in my SQL Server database that has 2 columns - 'UserID' and 'HoursWorked'. I need to return distinct HoursWorked values for each UserID on a given day to find out how many hours that particular person has worked.
The problem is that if a userID has worked in 2 or 3 departments in one day,they will be entered 2 or 3 times with part day entries into 'HoursWorked'.I need a query that will find these duplicates and then add the HoursWorked values together and display the User as just 1 entry. I was thinking I could maybe use a cursor but would appreciate further advice. Can anyone please help?

Many thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-22 : 08:51:16

Is this?

Select UserId, Sum(HoursWorked) from yourTable group by UserId

Otherwise post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-22 : 09:24:21

Thanks,
How would this affect the rows that have only one entry? I cannot get my hands on the data until Monday, so I cannot test it right away

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-22 : 09:29:00

It will work for all rows
To know that wait till Monday

Madhivanan

Failing to plan is Planning to fail

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-22 : 09:53:18

But I only want to select the rows where the UserID appears more than once.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-22 : 10:04:26

hope this works for u ..
Select UserId, Sum(HoursWorked) from yourTable group by UserId
Having Count(UserID) < 1

Complicated things can be done by simple thinking

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-22 : 11:02:26

But I only want to select the rows where the UserID appears more than once

quote:
Originally posted by chiragkhabaria

hope this works for u ..
Select UserId, Sum(HoursWorked) from yourTable group by UserId
Having Count(UserID) < 1

Complicated things can be done by simple thinking

Having Count(UserID) < 1 - Typo?
It should had been this way right?, Having Count(UserID) > 1

Thanks

Karunakaran

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-22 : 11:44:23

quote:
Originally posted by karuna

But I only want to select the rows where the UserID appears more than once

quote:
Originally posted by chiragkhabaria

hope this works for u ..
Select UserId, Sum(HoursWorked) from yourTable group by UserId
Having Count(UserID) < 1

Complicated things can be done by simple thinking

Having Count(UserID) < 1 - Typo?
It should had been this way right?, Having Count(UserID) > 1

Thanks

Karunakaran