February 4, 2011 at 11:42 am
Hi,
I have these sets of records:
AgentKey Datekey TimeKey EventType
--------------------------------------
1 20100901 0800 2
1 20100901 1100 3
1 20100901 1200 2
1 20100901 1700 3
2 20100901 0911 2
2 20100901 1212 3
2 20100901 1300 2
2 20100901 1856 3
where 2 - logintime
and 3 is logoff time. TmeKey is integer values
I need to calculate the total time worked by each agent.
i.e Agent 1 8:00 - 11:00 3 hours
12: - 17:00 5 hours
total 8 hours
February 4, 2011 at 11:52 am
First, select all clock-ins, with their corresponding clock-outs.
I usually would do that something like this:
select PersonID, MyDateTimeColumn as ClockIn,
(select min(MyDateTimeColumn)
from dbo.MyTable as MT2
where MT2.PersonID = dbo.MyTable.PersonID
and dbo.MyTable.MyDateTimeColumn > MT2.MyDateTimeColumn
and MT2.Status = 3) as ClockOut
from dbo.MyTable
where Status = 2;
Then, from that, select the DateDiff on the two DateTime columns. You'll probably want to use minutes for that, might need to use seconds, will depend on your purpose here.
Once you have that, you use the sum function and group by PersonID and the date.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply