Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Finding lapse times in table
Message
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Miscellaneous
Thread ID:
01338395
Message ID:
01338411
Views:
13
(trying to format better)

I actually have the time broken down to blocks of 15 minutes where 12:00AM to 12:15 as block 1, 12:15AM to 12:30AM as block 2, 12:30AM to 12:45 as block 3 etc.. The days worked are broken down to a week number and day number since 01/01/2000 (Monday is day 1)

so my table looks like
fk_employee      fk_week      fk_day      kk_timein      fk_timeout
1234                    395           1           58             62    
    (representing 7/21/2008 from 1:45PM to 2:45PM;
                which is 4  15 minute intervals or a total of 1hr)
1234                    395            1          64             74     
       (representing 7/21/2008 from 3:15PM to 5:45PM)
              which is 10 15 minute intervals for a total of 1.5hrs)
So in this case we would have a 30 minute lapse time


>
>>This is just a rough idea.
>>
>>Let say that you have data in the TaskSched table with the structure like
>>
>>
>>TaskNumber   StartTime         EndTime
>>1                    8:50                 9:00
>>2                    9:30                 11:00
>>3                    11:00                12:30
>>4                     14:00                15:00
>>you can self join this table like
>>
>>
>>SELECT T1.lnSequence,T1.EndTime as StartTime,T2.StartTime as EndTime  FROM TaskSched T1 INNER JOIN  TaskSched T2 ON T1.lnSequence+1=T2.lnSequence INTO CURSOR GAP
>>
>>
>>Like I said this is just rough idea. It would help to know the structure of your data
>>
>>>Hoping for some suggestions since we are dealing with approx 1400 employees, 7 days a week and need to determine each week before payroll run which employee's need to be compensated for those lapse time less than 1.5 hours each day.
>>>
>>>I also have to go through the last years time records and identify each employee each day that would qualify. Quite tedious and was hoping for suggestiongs to accomplish. Of the approx 1400 time cards recieved for each day and tasks can be from 1 to over 12 per day per employee.
>>>
>>>
>>>>I don't think you can solve this problem with SELECT-SQL statement (may be some really genious solution exists), but with the procedural code of looping through each employee and then each tasks per employee sounds like a pretty straightforward approach.
>>>>
>>>>In other words, get the cursor of the Employees with Tasks for given date and then loop through this cursor for each employee and see times elapsed between tasks using some variables.
>>>>
>>>>>Table with employee's starting time and ending time recorded for performing tasks.
>>>>>Times are recorded to the nearest 15 minutes such as 9:15 to 10:30 etc.
>>>>>
>>>>>An example would be employee scheduled for tasks from
>>>>> 8:00AM to 9:00AM (task 1)
>>>>> 9:30AM to 11:00AM (task 2)
>>>>>11:00AM to 12:30PM (task 3)
>>>>> 2:00PM to 3:00PM (task 4)
>>>>> 4:15PM to 6:00PM (task5)
>>>>>
>>>>>
>>>>>
>>>>>What is needed is to determine all thoes employee's who have a lapse of 15 minutes to 1.5 hours between tasks, so the above I would need to identify that the employee lapse of time of lapse time of 30 minutes from 9:00AM to 9:30AM (task2 to task 3) and lapse time of 1hr 15 minutes from 3:00PM to 4:15PM (task4 to task5)
>>>>>
>>>>>What is happening is that we are scheduling employees during the day and if we schedule less than 1 hour 30 minutes between tasks, we are required to compensate them for that time. If the lapse time is 1 hour 30 minutes or more, we are not required. Like wise if the lapse time is 0 (back to back) such as task 1 ending at 11:00AM and then task 2
>>>>>starting at 11:00AM M there is no lapse and therefore not required.
>>>>>
>>>>>Looking for the best solution to identify all employees on a give day that would fall into these lapse of times from 15 minutes to 1 hour 30 minutes.
>>>>>
>>>>>Thanks in advance for all suggestions.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform