Complex Time Calculation

[Guest]

I have a StartDateTime & FinishDateTime for a roster. The Duty Period (DP) is
Finish minus Start. I need to be able to calculate the total duty worked over
the previous 24, 48 and 72 hour periods, calculated from each FinishDateTime,
so I may need to calculate all, part or none of the previous DP's.
eg
1/4 off
2/4 off
3/4 1000 - 1600
4/4 0800 - 1400
DP
1/4 = 0
2/4 = 0
3/4 = 6
4/4 = 6
24 hour total
1/4 = 0
2/4 = 0
3/4 = 6
4/4 = 8 (4/4 dp = 6 + 2 hours between 1400 & 1600 on 3/4)
48 hour total
1/4 = 0
2/4 = 0
3/4 = 6
4/4 = 12
etc
I hope someone enjoys this challenge as I have been trying for a long time
to figure it out
Thanks
Warren

 

[Allen Browne]

For each entry in tblRoster, you want to sum the hours the employee worked
for the 48 hour period (not calendar days) prior to the close of this shift?
Then same thing for 72 hour-period etc.

A subquery will be the simplest way to get that. The following example
calculates the 48-hour period. You type the expression into the Field row of
your query into tblRoster. For the 72-hour period, type a similar expression
into the next column, and so on.

The WHERE clause selects roster shifts by comparing them to the record in
the main query. The other record must:
- be for the same employee, and
- start before this shift ends, and
- end after 48 hours prior to the end of this shift, and
- not be this same shift.

Having selected the shifts, you then want to sum the shift durations. But if
the matching shift began more than 48 hours ago, you want to start from 48
hours back, not the beginning of the shift. The IIf() expression chooses the
starting time.

The subquery then sums the difference in minutes from all such shifts, and
divides by 60 so you get fractions of an hour.

This is untested and you will need to do your own debugging, but the kind of
expression you are after will be something along these lines:

DP: (SELECT Sum(DateDiff("n",
IIf(Dupe.StartDateTime >= DateAdd("d", -4, tblRoster.EndDateTime),
Dupe.StartDateTime, DateAdd("d", -4, tblRoster.EndDateTime)),
Dupe.EndDateTime)) AS Minutes
FROM tblRoster AS Dupe
WHERE (Dupe.EmployeeID = tblRoster.EmployeeID)
AND (Dupe.StartDateTime < tblRoster.EndDateTime)
AND (Dupe.EndDateTime > DateAdd("d", -4, tblRoster.EndDateTime))
AND (Dupe.RosterID <> tblRoster.RosterID)) / 60

For the other queries, substitute -1, -3 etc for the -4 inside the DateAdd()
expressions.

If subqueries are new, Microsoft's introduction is:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

HTH