Calculating Hours worked?

[lespaul]

Thanks to everyone who has helped me on this jury project over the past 6 months or so!! I'm on one of the last pieces and I'm not sure how to gather the information in the correct format. I have a query that returns the name, address, and roundtrip miles for each person that needs to be processed. For each person in that dataset, I run a second query that returns the days each person worked with the time in and out.

JurNum ServDate TimeType TimeIO
757 20021223 OI 0830
757 20021223 OO 1030
757 20021226 TI 0900
757 20021226 TO 1100
757 20021230 TI 0900
757 20021230 TO 1400

I need to take the above information and come up with:
20021223 2.0
20021226 2.0
20021230 5.0
Total Hours 9.0

Number of Trips = 3 (they came to the courthouse 3 days)
RTM = 16
Total Miles : 48

Does anyone have any suggestions on the best way to calculate the hours worked and figure out how many trips the person made? Thanks!! Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[towerbase]

What is the significance of the TimeTypes 'OI' and 'OO' or are they typos for 'TI' and 'TO' ?

Surely you need the Jury Number in the following data?

20021223 2.0
20021226 2.0
20021230 5.0
Total Hours 9.0

Something like the following should work

SELECT a.JurNum, a.ServDate, b.TimeIO - a.TimeIO 
FROM table a, table b
WHERE a.JurnNum=757 AND a.JurNum=b.JurNum AND a.ServDate=b.ServDate AND a.TimeType='TI' AND b.TimeType='TO'

would get you the first three lines. The total hours is simply the sum of those lines. You should be able to devise some SQL for the rest.

It is not clear to me where you get the miles.

Andrew

 

[lespaul]

That would work great if our database had a date/time field! (Great SQL - I'll have to keep that for future reference!!) But the times are stored as integers and I have a Delphi function that converts them to times.

Here's what I've come up with so far:
qryHoursCalc returns the Jurnum, name, address, and roundtrip miles for each person for a particular date range (selected earlier in the process). ProcessList is an OLEVariant array that I pass to Excel for printing purposes.
For each person in qryHoursCalc I run qryTemp that returns all the hours entered for that person. (BTW - OI is orientation In, OO is orientation out, TI - Time In, TO - Time Out).
JurNum ServDate TimeType TimeIO
757 20021223 OI 0830
757 20021223 OO 1030
757 20021226 TI 0900
757 20021226 TO 1100
757 20021230 TI 0900
757 20021230 TO 1400

Then I count the number of records returned (in this case 6) and divide it by 2 to get the number of trips made to the courthouse. Then I make an array that's (in this case) 3 rows, 3 columns. Then I loop through the record set and if an even number (i) then (i,0) equals the date. If the type has an I then (i, 1) equals the timeIO, else if type has an O then (i, 2). So I end up with an array of TDateTimes that looks like:

12/23/2002 8:30 10:30
12/26/2002 9:00 11:00
12/30/2002 9:00 2:00

Then I can calculate the difference in the times and calculate the hours worked. Sound like it will work?
(ps - the code below isn't quite as complete as my description, but that's where I'm heading with it). Once I have the hours worked I can fill in the ProcessArray with the person's name, address, roundtrip miles, hours worked, amount owed for hours, amount owed for mileage and dump it into Excel. (ps - I have to loop through to count the records because RecordCount doesn't work with our database - AS400).

If not JMSData.qryHoursCalc.IsEmpty then
    begin
      personcount := 0;
      While not JMSData.qryHoursCalc.EOF do
      begin
        inc(personcount);
        JMSdata.qryHoursCalc.Next;
      end;
      ProcessList := VarArrayCreate([0, (personcount - 1), 0, 7], varOleStr);
      JMSData.qryHoursCalc.First;
      For i := 0 to (personcount - 1) do
      begin
        JMSData.qryTemp.SQL.Clear;
        JMSData.qryTemp.SQL.Add('SELECT * FROM JMPHOURS WHERE JURNUM = ' +
          JMSData.qryHoursCalc.FieldByName('JURNUM').AsString);
        JMSData.qryTemp.Active := True;
        While not JMSData.qryTemp.EOF do
        begin
          inc(daysworked);
          JMSdata.qryTemp.Next;
        end;
        tripsmade := daysworked div 2;
        SetLength(HoursWorked, tripsmade, 3);
        JMSData.qryTemp.First;
        for i = 0 to daysworked - 1 do
        begin
          if i mod 2 = 0 then
            Hoursworked(i, 0) := StrToDate(formatDate(JMSData.qryTemp.FieldByName('SERVDATE').AsString));
        end;

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[LorenPechtel]

Caution--are you sure you have *ALL* records? What happens when someone fails to clock in or out?

I've done a timeclock before. My data records were stored with a clock-in and clock-out--if data was missing it was zero. The processing routine has to in some fashion deal with defective records. (My solution: There's a test and final mode. The test mode gives a list of errors. If they haven't been fixed in final mode the system simply skips over that record--nothing is paid but the record is not marked as paid, either. It will continue to show as an error until someone does fix it.)