spacific time

[jbarna]

Hi i am at a total loss.
My question is i have a start time and end time in a form to run jobs.
how do i look between the start time and end time to see if 11:30 am
falls between the start time and end time and then subtract 30 mins
for lunch.
thanks sooo much

 

[James A. Fortune]

Hi i am at a total loss.
My question is i have a start time and end time in a form to run jobs.
how do i look between the start time and end time to see if 11:30 am
falls between the start time and end time and then subtract 30 mins
for lunch.
thanks sooo much

Your specific question is not particularly difficult, but its
generalization is trickier.

In:

http://groups.google.com/group/comp.databases.ms-access/msg/44ed54b162a1aa3c

I said:

"Here's a rudimentary and nearly totally untested start at using SQL to
do the same thing:

tblTimeTickets
TTID AutoNumber
PunchIn Date/Time
PunchOut Date/Time
TTID PunchIn PunchOut
1 12/19/05 9:15:00 AM 12/23/05 10:30:00 AM
2 12/22/05 9:30:00 AM 12/23/05 5:30:00 PM


tblBreaks
BID AutoNumber
BreakStart Date/Time formatted h:nn ampm
BreakEnd Date/Time formatted h:nn ampm
BID BreakStart BreakEnd
1 9:30 AM 9:45 AM
2 11:00 AM 11:15 AM
3 11:15 AM 11:30 AM
4 12:30 PM 12:45 PM


qryCountBreaks:
SELECT TTID, BreakStart, BreakEnd, IIf(Abs(Int(PunchIn)+(BreakEnd)
Between PunchIn And
Punchout)=0,0,DateDiff('n',Int(PunchIn)+(BreakEnd),PunchOut)\60\24)+1
AS CountBreaks FROM tblTimeTickets, tblBreaks;


!qryCountBreaks:
TTID BreakStart BreakEnd CountBreaks
1 9:30 AM 9:45 AM 5
2 9:30 AM 9:45 AM 2
1 11:00 AM 11:15 AM 4
2 11:00 AM 11:15 AM 2
1 11:15 AM 11:30 AM 4
2 11:15 AM 11:30 AM 2
1 12:30 PM 12:45 PM 4
2 12:30 PM 12:45 PM 2


Analysis:


The query is not quite correct as it stands. It uses the BreakEnd
value on the day of PunchIn to see if the value lies between the
PunchIn and PunchOut. If it does, that one is counted and one more is
added for each 24 hour period after that BreakEnd value that is
contained between PunchIn and PunchOut. A BreakEnd time slightly after
midnight needs to be considered. The test example using some poor
workaholics avoids some obvious problems conveniently. I also didn't
test to see if 59.75 minutes gets converted to 60 by the DateDiff
function. DateDiff using hours proved unsatisfactory. I probably
should have gone directly to the representation of dates as a Double
value to determine this difference. Post back if you need more hints."


I'm more than a little embarrassed by how I did this given my current
practice of using Date functions so that I don't rely on Access'
internal representation of dates.

I'll try to come up with something better. I expect that others will
provide solutions to your specific question while I do that.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

I'll try to come up with something better. I expect that others will
provide solutions to your specific question while I do that.

The way I would answer that post today might include:

qryCountBreaks:
SELECT TTID, BreakStart, BreakEnd, DateAdd("n", Hour(BreakEnd) * 60 +
Minute(BreakEnd), DateValue(PunchIn)) AS dt1, DateAdd("n",
Hour(BreakEnd) * 60 + Minute(BreakEnd), DateValue(PunchOut)) AS dt2,
IIf(DateDiff("d", dt1, dt2) < 2, 0, DateDiff("d", dt1, dt2) - 1) +
Abs(dt1 > PunchIn AND dt1 <= PunchOut) + Abs(dt1 <> dt2) * Abs(dt2 >
PunchIn AND dt2 <= PunchOut) AS CountBreaks FROM tblTimeTickets, tblBreaks;

That is, compute the end of the break on the PunchIn and PunchOut dates
(dt1 and dt2 contain the computed date values). Then count one break
for every full day between dt1 and dt2 that doesn't include dt1 or dt2,
then check the dates that include dt1 or dt2 recognizing that those
counts will be double if dt1 = dt2. Since dt1 and dt2 have the same
h:nn values there is always an integral number of days between them.
The SQL above seems to handle more situations than the previous solution.

I use comparison operators instead of using BETWEEN because I don't want
to count a break if it ends exactly where a PunchIn starts. BTW, by
using BreakStart in the calculations instead of BreakEnd and by shifting
the '=' for the date range comparison it's possible to move the closed
end of the interval to the beginning of the interval.

Since the durations in tblBreaks are all the same, counting breaks is
equivalent to counting the amount of time on break. You can use
DateDiff (with "n") to get the duration of an entire shift in minutes,
then subtract the break time.


So, to tell if a time (including the date), ti, is between two other
times, ts and te:

ti >= ts AND ti <= te

or

DateDiff('s', ts, ti) >= 0 AND DateDiff('s', ti, te) >= 0

or

ti BETWEEN ts AND te


I expect that regardless of the underlying implementation for dates, SQL
users will expect to be able to use comparison operators directly making
it quite unlikely that the current implementation of dates in Access or
SQL Server will change anytime soon. Still, I avoid performing
arithmetic operations on date values.

I tested the SQL using a break that ended soon after midnight and it
seemed to do the right thing for that situation. It may even be
worthwhile to think about including a shift number field in tblBreaks.
Be sure to test the SQL adequately if you use something like it for your
situation.

James A. Fortune
(e-mail address removed)