Can I calculate peak time usage?

[MRSPK]

Hello All,

Is it possible to generate a query that can calculate the 'TotalMins' that an event covers our peak times?

I.e. Our peak times are 0700 to 1000 if we have a start time of 0600 and a finish time of 0900 is it possible for the query to work out that we have covered 120 mins of the peak period?

Thanks for any help provided!

 

[Jon K]

You can build a public function in a Module.

Public Function calPeakMins(Start, Finish) As Variant
   Dim PeakStart As Date
   Dim PeakEnd As Date
   Dim UsageStart As Date
   Dim UsageEnd As Date
   
   PeakStart = #7:00:00 AM#
   PeakEnd = #10:00:00 AM#
   
   If IsNull(Start) Or IsNull(Finish) Then
     Exit Function
   End If
   If Finish < PeakStart Or Start > PeakEnd Then
     [b][color=blue]calPeakMins = 0[/color][/b]
     Exit Function
   End If
   
   UsageStart = IIf(PeakStart > Start, PeakStart, Start)
   UsageEnd = IIf(PeakEnd < Finish, PeakEnd, Finish)
       
   calPeakMins = Round((UsageEnd - UsageStart) * 24 * 60, 2)
   
End Function

Then in a query, you can pass the start time and end time to the function like this:

PeakMins: calPeakMins([StartTime], [FinishTime]) +0


Note:
The function is made to return a variant instead of a number so that it can handle null values, if any, in the [StartTime] and [FinishTime] fields.

In the expression, the +0 is used to convert the returned variant to a number.


Edited: Added the blue line in the code.
.