Compare Time Intervals (1 Viewer)

Hej:

I have a problem with comparations of time intervals:
For example my data is:

Table1 (USER DATA)
REG DAY TIMEIN TIMEOUT
1 1 O7:30 16:00
2 7 07:30 16:30
3 1 07:30 16:00
4 2 07:00 16:30
5 3 08:00 15:00
6 7 17:00 19:00

Table2 (CONTROL DATA)
REG DAY TIMEIN TIMEOUT PORCENT
1 1 07:30 16:00 25%
2 1 16:01 20:00 50%
3 7 07:00 16:00 35%
4 7 16:01 20:00 75%

By hand, for Reg 1 of Table1 correspond to Reg 1 of Table 2, Reg 2 of Table1 correspond Reg 3 of Table2 (for 07:30 to 16:00) and Reg 4 of Table2 (for 16:00 to 16:30), that give me the diff of time and porcentage to apply.

Is posibble make a query for get de correspond reg of Table2 by values of Table1, showing the diff of hours.

The problematic is get de correct porcent to apply to data of Table1 from Table2.

Any idea for a query or function for that.

Thanks in advance.

Have a look at the DateDiff() function, you can use that to determine the difference between two dates/times, in hours, minutes, seconds or days, weeks months etc. From there you should be able to do your own calculation to calculate the percentage

Thank for your comment, but I think you don´t read right or I not explain well.

My problem is get the correspond interval from Table2 about reg in Table1 not get the difference between two dates/times.

Example:

I have a reg in Table1: Day 1 TIMEIN 07:30 TIMEOUT 16:00, for this I need the correspond interval from Table2 for get the porcentage to apply to total time between two dates/times.

By hand, a hard example is reg 2 from Table1:

One part of the interval of reg 2 of Table1 correspond to reg 3 of Table2 (for 07:30 to 16:00 interval of) and reg 4 of Table2 (for 16:00 to 16:30 interval), that means, I need appy two different porcentage to a one reg of interval from Table1.

If the Table1 regs are Time of Work and regs of Table2 are porcentage to apply to correspond hours of work, when a reg of Table1 are between two reg of Table2, the simple query result show only one reg to apply to Time Work data for each one of Table1 about Table2.

Right calculations:

If I get a reg with Time of Work of 07:30 to 16:30 (reg 2 of Table1 for Day number 7), the correct result will be to apply reg 3 and 4 of Table2 for a day number 7, that is, for the part (07:30 16:00) of Time Interval of reg 2 of Table1 I need to apply 35% (from reg 3 of Table2) and for the part (16:01 16:30) of Time Interval of reg 2 of Table1 I need to apply 75% (from reg 4 of Table2).

Table1 have about 1000 regs of Time of Work and Table2 have few intervals of different porcentage to apply to Time of Work.

By code I can use conditionals and statements like IF and <= >= ... for get the porcentage for each part of intervals of Time of Work. But my request is for there is a way to get the calculations params with access query and/or procedure/function to apply to a field of the some query.

I make a query with DAY field related between this tables, using comparations of TIMEIN with TIMEIN and TIMEOUT of two tables, but some times I get duplicate reg or missing reg from Table1.

Example:
"SELECT NUMDAYFROMTASKS.IDTASKS, NUMDAYFROMTASKS.FROMTIME, NUMDAYFROMTASKS.TOTIME, DAYOVERTIME.FROMTIME, DAYOVERTIME.TOTIME, DAYOVERTIME.PORCENTTIME
FROM NUMDAYFROMTASKS INNER JOIN DAYOVERTIME ON NUMDAYFROMTASKS.NUMDAY = DAYOVERTIME.NUMDAYTIME
WHERE (((NUMDAYFROMTASKS.FROMTIME)<=[DAYOVERTIME].[FROMTIME]) AND ((NUMDAYFROMTASKS.TOTIME)<=[DAYOVERTIME].[TOTIME]));
"

Thanks in advance

What information/key are you using to uniquely link the data in your two tables?

I make a query with IIF sub conditionals and get this result:



The only problem is with first results for IDTASKS 258 and 260, the HORAS result should be 8 not 7.5, but I can´t get the difference between OVERFROM AND OVERTO for intervlas of 07:30:00 15:00:00 and 15:01:00 16:00:00.

The logic for that is:

IIf([TOTIME]>=[OVERTO] AND OVERFROM > FROMTIME,Round(DateDiff("n",[OVERFROM],[OVERTO])/60,2)

This condition is equal for both intervals. The complete condition is:

IIf([TASKS].[TOTIME]>=[BASECALCULO].[OVERTO] AND BASECALCULO.OVERFROM > TASKS.FROMTIME,Round(DateDiff("n",[BASECALCULO].[OVERFROM],[BASECALCULO].[OVERTO])/60,2),
IIf([TASKS].[TOTIME]>=[BASECALCULO].[OVERTO] AND BASECALCULO.OVERFROM <= TASKS.FROMTIME,Round(DateDiff("n",[TASKS].[FROMTIME],[BASECALCULO].[OVERTO])/60,2),
IIf([TASKS].[TOTIME]<=[BASECALCULO].[OVERTO] AND BASECALCULO.OVERFROM > TASKS.FROMTIME,Round(DateDiff("n",[BASECALCULO].[OVERFROM],[TASKS].[TOTIME])/60,2),
IIf([TASKS].[TOTIME]<=[BASECALCULO].[OVERTO] AND TASKS.FROMTIME<= BASECALCULO.OVERFROM,Round(DateDiff("n",[TASKS].[FROMTIME],[TASKS].[TOTIME])/60,2),
0))))
AS HORAS

Any idea will be apreciated, thank in advance.

Re: Solved Compare Time Intervals

I forget my life, the result is rocky, I need to add the rest of 0.5 hours in normal time for reg 258 and 260 and all is OK.