time events

[tezzyr]

Hi,
I have an application which uses crIX on an sql express database. The application deals with time and attendance data for employees of a company and basically stores the time they clock in of a morning, the time they clock in and out for breaks/dinner etc during the day and the time they clock off when they leave work for the day.

The events are all stored in the 'event' table and are timestamped with the date/time the employee clocked in or out.
A clocking in event has an eventtype 1 and a clocking out event has an eventtype 2.

For each day, an employee will have the following events as they clock in at 9, out for lunch at 1, back from lunch at 2 and out at 17:30 when they leave work for the day:-

eventid eventtype employeename time
1 1 fred 04/04/2007 9:00:00
5 2 fred 04/04/2007 13:00:00
24 1 fred 04/04/2007 14:00:00
35 2 fred 04/04/2007 17:30:00

My question is, how do i show how long an employee has been in work so that my report looks as below:-

Employeename time in time out duration
fred 04/04/2007 9:00 04/04/2007 17:30 7 hrs30mins

showing only the first in/last out event for that employee on that day. Working out the duration seems pretty straight forward (just do a datediff on the values i presume and convert number of seconds back into hours and mins) but I've no idea how to show only the first and last events on the report, seeing as there may be more than one clocking in and out events for each day.

Thanks in advance!

 

[Madawc]

Tricky. I can see one way.

Find the difference between each line and the previous line, suppressed for the first line. (I assume you know DateDiff, which includes time. Previous is Previous({your.date})

From this value, produce a running total using a formula. If the event type is 2, accumulate it.

I've not actually done this, but it ought to work. Show the fields along-side the data while developing the report, remove them later when it's something to pass on to the users.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

If you group on employee and then on {table.datetime} on change of date, you could just insert a minimum and a maximum on the datetime field to get the display you want.

-LB