sum working hours given multiple starts/ends

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

Transact-SQL (2000)

sum working hours given multiple starts/ends

Author

Topic

Bill Schneider
Starting Member

5 Posts

Posted - 2005-10-18 : 08:23:00

I have a table with start and end times for persons.
These entries come from a time recording system people use here.

PersonID Time comesorgoes
1 10/18/2005 7:00 comes
1 10/18/2005 9:00 goes
1 10/18/2005 11:00 comes
1 10/18/2005 14:00 goes
... for other people the same

The sum of working hours in this example is 5 hours. How do I get the sums for all people per day (a work that starts one day always ends the same day)? How to add the condition that the earliest possible start time for payment is 7:00 (people can punch in their start time before 7:00 but get paid only after)?

Perhaps someone can help me with this problem.
Thank you.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-18 : 08:35:54

[code]
select personid,
sum(case when comesorgoes = 'comes' then time else -time end)
from (select personid,
case
when (select min(time) as time from table1 where
personid = t1.personid)
< DATEADD(hh, 7, DATEDIFF(d, 0, time))
then DATEADD(hh, 7, DATEDIFF(d, 0, time))
else time
end,
comesorgoes
from table1 t1) t
group by personid
[/code]

Go with the flow & have fun! Else fight the flow

Bill Schneider
Starting Member

5 Posts

Posted - 2005-10-18 : 09:47:59

Thank you!

I have used your answer this way.

CREATE TABLE [dbo].[table1] (
[PersonID] [bigint] ,
[TheTime] [smalldatetime] ,
[comesorgoes] [char] (10))

insert into table1(personid,thetime,comesorgoes) values(1,'17-10-2005 6:30','comes')
insert into table1(personid,thetime,comesorgoes) values(1,'17-10-2005 8:30','goes')
insert into table1(personid,thetime,comesorgoes) values(1,'17-10-2005 10:30','comes')
insert into table1(personid,thetime,comesorgoes) values(1,'17-10-2005 16:30','goes')
insert into table1(personid,thetime,comesorgoes) values(1,'16-10-2005 6:30','comes')
insert into table1(personid,thetime,comesorgoes) values(1,'16-10-2005 16:30','goes')

This works perfectly:

select personid, sum(case when comesorgoes='comes' then -cast(thetime as float) else cast(thetime as float) end) as Sum
from table1
group by personid

But when I try to group also by the day it does not work:

select personid, sum(case when comesorgoes='comes' then -cast(thetime as float) else cast(thetime as float) end) as Sum, CAST(thetime AS char(11)) as AtDay
from table1
group by personid,AtDay

Do you have an idea?

dsdeming

479 Posts

Posted - 2005-10-18 : 10:37:50

Have you tried this:

group by personid,CAST(thetime AS char(11))

Dennis

Bill Schneider
Starting Member

5 Posts

Posted - 2005-10-19 : 05:35:46

Thank you. It works.

For spirit1:
At first I only saw the first 2 lines of your answer. Only later I saw it complete.
So thank you again.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-19 : 05:37:39

yeah... i edited it later and added stuff.

Go with the flow & have fun! Else fight the flow