Sum of hours
-
Hi,
I have this problem....
I have table with information about work time of worker on car....seems like this
CodUser,CodCar,Date,From,To (From,To is int where I save minutes 120,240,125,...)
User can in same time work on 2 or more cars...so data can be like this
1,1,2005/01/01,120,240
1,2,2005/01/01,150,280
1,3,2005/01/01,200,300
1,1,2005/01/01,310,450
And now I need sum of hours that worker work par day....mean if he work in 1 hour on 3 cars, for me he work 1 hour - I need know how many hours he stay in work.
Do you have somebody some idea? Thanx
-
Hello Gofrin,
Can you check out this query
select coduser, codcar, stdate, (sum([to])- sum([from]))/60.0 as totalhrs
from <ur table name>
group by coduser, codcar, stdate
Thanks and have a nice day!!!
Lucky -
Ok, but with this you have sum of hour thath worker do on all cars....so he can work more then 24 per day....I need this
start work on car 1 - 8:30 to 10:00
on car 2 - 9:00 - 11:00
on car 3 - 10:00 - 12:45
so worker start in 8:30 and finish 12:45 - total is 4,25h (4h 15min)
-
How can 1 person work on 2 physically separate cars at the same time ?
Is this why I get billed so much for "labor" when I get my car serviced ?
You need a join of 2 derived tables, where 1 gives you the earliest start time per person per day, and the other gives the latest:
Select dtStart.CodUser, dtStart.Date, dtEnd.EndTime - dtStart.StartTime As MinutesWorked
From
(
Select CodUser, Date, Min(From) As StartTime
Group By CodUser, Date
) dtStart
Inner Join
(
Select CodUser, Date, Max(To) As EndTime
Group By CodUser, Date
) dtEnd
On (dtStart.CodUser = dtEnd.CodUser And
dtStart.Date = dtEnd.Date)
-
I know that is strong but this table is mainly used for car not for user
ok good query, but I need how many hours he work not when he start and when he finish
- for me is important that he work 2 hours and he can start from 8:00-9:00 and then continue in 17:30-18:30 (realy big pause) -
So, you have to handle overlapping and non-overlapping periods ?
Since your granularity is to the minute, I would build a table containing all workable minutes in the day - this would have a maximum of 1440 rows. Midnight (zero) to 11:59pm (1439).
If you had this table, call it AllMinutes, you could join to it and COUNT() the distinct set of minutes worked. This would eliminate duplicates from overlaps, and handle gaps:
Select COUNT(Distinct AllMinutes.Minute) / 60.0 As HoursWorked
From YourTable
Inner Join AllMinutes
On (YourTable.From >= AllMinutes.Minute And
YourTable.To <= AllMinutes.Minute)
-
Very nice idea, PW...
An alternative - not at all better, but it's a different approach....
declare @YourTable table(CodUser int, [From] int, [To] int)
insert @YourTable select 1, 120, 240
insert @YourTable select 1, 150, 280
insert @YourTable select 1, 200, 300
insert @YourTable select 1, 310, 450
declare @start table(id int identity(1, 1), CodUser int, StartTime int)
declare @end table(id int identity(1, 1), CodUser int, EndTime int)
insert @start (CodUser, StartTime)
select distinct t1.CodUser, t1.[From]
from @YourTable t1 left join @YourTable t2
on t1.CodUser = t2.CodUser
and t2.[From] < t1.[From] and t1.[From] <= t2.[To]
where t2.[From] is null
order by t1.CodUser, t1.[From]
insert @end (CodUser, EndTime)
select distinct t1.CodUser, t1.[To]
from @YourTable t1 left join @YourTable t2
on t1.CodUser = t2.CodUser
and t2.[From] <= t1.[To] and t1.[To] < t2.[To]
where t2.[From] is null
order by t1.CodUser, t1.[To]
select
s.codUser, sum(e.EndTime - s.StartTime)
from @start s inner join @end e
on s.Id = e.Id
group by s.CodUser
-
thanx you very much, I will try both and check what will be better for me
-
Much easier than what I proposed before
declare @YourTable table(CodUser int, [From] int, [To] int)
insert @YourTable select 1, 120, 240
insert @YourTable select 1, 150, 280
insert @YourTable select 1, 200, 300
insert @YourTable select 1, 310, 450
select dt.CodUser, sum(dt.SignedTime)
from
(
select distinct t1.CodUser, (-1)*t1.[From] as SignedTime
from @YourTable t1 left join @YourTable t2
on t1.CodUser = t2.CodUser
and t2.[From] < t1.[From] and t1.[From] <= t2.[To]
where t2.[From] is null
union all
select distinct t1.CodUser, t1.[To] as SignedTime
from @YourTable t1 left join @YourTable t2
on t1.CodUser = t2.CodUser
and t2.[From] <= t1.[To] and t1.[To] < t2.[To]
where t2.[From] is null
)
dt
group by dt.CodUser
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply