calculating total hours worked

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)

calculating total hours worked

Author

Topic

bhavani
Starting Member

1 Post

Posted - 2003-04-27 : 22:07:14

hi all,

Can anyone tell me the solution to my problem....
i want to calculate the total hours worked by each employee. there are 100 cardnumbers.
my table structure:
EID EDATETIME CARDNUMBER
1 2003-04-25 14:26:27.000 10024
1 2003-04-25 14:26:44.000 10054
2 2003-04-25 14:26:03.000 10024
2 2003-04-25 14:26:14.000 10054
like the above there are thousands of records storing the in-time and out-time.

EID 2 means exit and 1 is entry

i want to calculate the total working hours and first-in and last-out of each employee (ASP AND SQL SERVER)

bye,

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-04-27 : 22:46:19

Look up joins, aggregate and date functions in Books Online.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-28 : 01:06:38

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12132

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-28 : 09:30:36

your sample data didn't make a lot of sense (it looked like the In times before after the out times ??) so I edited that, and also added a 3rd employee who took a 1 hour lunch break:

declare @t table
(EID int,
DT datetime,
CardNo int)

insert into @t
select 1,'2003-04-25 09:00 AM',10024 union
select 1, '2003-04-25 8:30 AM', 10054 union
select 2, '2003-04-25 06:00 PM', 10024 union
select 2, '2003-04-25 07:00 PM', 10054 union
select 1, '2003-04-25 09:00 AM', 10025 union
select 2, '2003-04-25 12:00 PM', 10025 union
select 1, '2003-04-25 1:00 PM', 10025 union
select 2, '2003-04-25 5:00 PM', 10025

select CardNo, SUM(DateDiff(mi,inTime,OutTime))/60.0 as Hours
FROM
(
select InTimes.CardNo, inTimes.DT as InTime, Min(OutTimes.DT) as OutTime
from
@t InTimes
left outer join
@t OutTimes
ON
InTimes.CardNo = OutTimes.CardNo AND
OutTimes.EID = 2 AND
OutTimes.DT > InTimes.DT
WHERE InTimes.EID = 1
GROUP BY InTimes.CardNo, inTimes.DT
)
a
group by CardNo

Note that there MUST be a corresponding OUT time for each IN time for this to work -- there can't be a sequence for a given employee like IN,IN,OUT or IN,OUT,OUT,IN .

You can round the hours as needed to the nearest quarter hour by using a formula like this:

convert(int, (TotalHours * 4)) / 4.0

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-28 : 10:33:51

ooops.. forgot the First In and Last OUt for each employee:

select CardNo, SUM(DateDiff(mi,inTime,OutTime))/60.0 as Hours,
Min(InTime) as FirstIn, Max(OutTime) as LastOut
FROM
(
select InTimes.CardNo, inTimes.DT as InTime, Min(OutTimes.DT) as OutTime
from
@t InTimes
left outer join
@t OutTimes
ON
InTimes.CardNo = OutTimes.CardNo AND
OutTimes.EID = 2 AND
OutTimes.DT > InTimes.DT
WHERE InTimes.EID = 1
GROUP BY InTimes.CardNo, inTimes.DT
)
a
group by CardNo

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources