Dear All,
I have written an adp project for Clocking in times. However I have come across a problem in my effort to speed up the project.
I import data with all the clock in and out times in one record per day per person and when calculating when someone goes out for lunch this isn't a problem.
However, in order to do the majority of my other calculations I've had to do a union query to to make each clock in and out time a seperate record. There are some very complex queries in this project so them all running off a union query is quite slow so I've decided to make the union query a permanent table when I import the data.
However, for calculating the time people go out for lunch I have struck a problem as each record has a clock in and a clock out time, but for the lunch calculation I need the clock out from one record and the clock in from the next record to calculate how long they've been out. If anyone knows any sql which can solve this it would be a great help I've come up stumps with trying to solve this.
example
00:00:00 = clock out for lunch
00:00:00 = clock in from lunch
Date ClockNumber Clock In Clock Out
09/06/06 1123 09:00:00 12:01:00
09/06/06 1123 12:05:00 12:45:00
09/06/06 1123 12:59:00 16:34:00
I basically need to subtract the first red from the first lime and so on then sum up the results for that date?
Thanks
Matthew
I have written an adp project for Clocking in times. However I have come across a problem in my effort to speed up the project.
I import data with all the clock in and out times in one record per day per person and when calculating when someone goes out for lunch this isn't a problem.
However, in order to do the majority of my other calculations I've had to do a union query to to make each clock in and out time a seperate record. There are some very complex queries in this project so them all running off a union query is quite slow so I've decided to make the union query a permanent table when I import the data.
However, for calculating the time people go out for lunch I have struck a problem as each record has a clock in and a clock out time, but for the lunch calculation I need the clock out from one record and the clock in from the next record to calculate how long they've been out. If anyone knows any sql which can solve this it would be a great help I've come up stumps with trying to solve this.
example
00:00:00 = clock out for lunch
00:00:00 = clock in from lunch
Date ClockNumber Clock In Clock Out
09/06/06 1123 09:00:00 12:01:00
09/06/06 1123 12:05:00 12:45:00
09/06/06 1123 12:59:00 16:34:00
I basically need to subtract the first red from the first lime and so on then sum up the results for that date?
Thanks
Matthew
Last edited:
