Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Ron .
  
 
 
 Ron .
 Tags
Subject: RE: Finding Lapse Times
Thread ID: 188979 Message ID: 188992 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Tuesday, August 12, 2008 9:58:26 PM         
   


> 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.

SELECT employeeid, task, starttime, endtime From Table ;
 ORDER BY employeeid, start INTO CURSOR Q1
SELECT * FROM Q1 WHERE 1=0 INTO CURSOR Compensations READWRITE
SELECT * FROM Q1 INTO CURSOR Q2
SET RELATION TO RECNO()+1 INTO Q1 IN Q2
SCAN 
 IF Q1.employeedid = Q2.employeedid AND BETWEEN(Q1.starttime - Q2.endtime,15*60, 90*60) 
  INSERT INTO Compensation VALUES (Q2.employeesid, q2.task, q2.starttime, Q2.endtime)
  INSERT INTO Compensation VALUES (Q1.employeesid, q1.task, q1.starttime, Q1.endtime)
 ENDIF 
ENDSCAN 


There's an SQL query solution but I'm off to watch a thriller.

-Anders

PS Need to add
AND Q2.date=Q1.date


-A

ENTIRE THREAD

Finding Lapse Times Posted by Ron . @ 8/12/2008 9:06:10 PM
RE: Finding Lapse Times Posted by Neil Fairbanks @ 8/12/2008 9:32:57 PM
RE: Finding Lapse Times Posted by Anders Altberg @ 8/12/2008 9:38:19 PM
RE: Finding Lapse Times Posted by Ron . @ 8/12/2008 10:05:28 PM
RE: Finding Lapse Times Posted by Anders Altberg @ 8/12/2008 9:58:26 PM