Date/Time overlap problem 1

[padinka]

This is a sampling of my data.


BU Invoice Dept DateOccurred ServiceRestored
EAST 100110125 1 8/23/00 10:20:00 AM 8/23/00 10:33:00 AM
EAST 100110125 2 8/23/00 10:24:00 AM 8/23/00 10:46:00 AM
EAST 100110125 3 8/23/00 10:26:00 AM 8/23/00 10:30:00 AM
EAST 100110125 4 8/23/00 10:35:00 AM 8/23/00 10:42:00 AM
EAST 100110125 5 8/23/00 10:36:00 AM 8/23/00 10:47:00 AM
EAST 100110125 6 8/23/00 2:16:00 PM 8/23/00 2:27:00 PM
EAST 98558255 2 8/26/00 7:43:29 AM 8/26/00 8:15:00 AM

Each department has an individual down calculated by subtracting the DateOccurred from the ServiceRestored. Each down time can overlap other departments down times. My problem is that I need to calculate the total number of minutes the system was down for the Business Unit. (In this sample case for Invoice 10010125 it would begin at 10:20 and go till 10:47 then start again at 2:16 and go till 2:27 for a total of 38 minutes of downtime.)

Any suggestions???
[sig]<p>Trisha<br><a href=mailtoadinka@yahoo.com>padinka@yahoo.com</a><br>[/sig]

 

[MichaelRed]

Trisha,

This function 'works' on your sample data. I assume there is more to the problem than stated, so 'adjustments' will probably be necessary. I took some liberties w/ the field names, I think you can see what this is all about. [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[MichaelRed]

Sorry about that

Public Function basData()

Dim dbs As Database
Dim rstInv As Recordset
Dim rstMyInv As Recordset
Dim qdfInv As QueryDef

Dim strSql As String
Dim TmStrt As Date
Dim TmEnd As Date
Dim CummTime As Integer
Dim blnNewIntvl As Boolean

Set dbs = CurrentDb

'SQL for 'qryInvoices'

'SELECT DISTINCT tblSvcRec.BusUnit, tblSvcRec.InvNum
'FROM tblSvcRec;

Set rstInv = dbs.OpenRecordset(&quot;qryInvoices&quot;, dbOpenDynaset)

Do While Not rstInv.EOF 'Process each Invoice as a seperate recordset

'SQL for 'qryMyINvoice'. Note PARAMETER [MyInv]!
'SELECT tblSvcRec.*, tblSvcRec.InvNum
'FROM tblSvcRec
'WHERE (((tblSvcRec.InvNum)=[MyInv]));

Set qdfInv = dbs.QueryDefs(&quot;qryMyInvoice&quot
qdfInv.Parameters(&quot;MyInv&quot = rstInv!InvNum
Set rstMyInv = qdfInv.OpenRecordset()

TmStrt = rstMyInv!dtoccur 'Start Time for Outtage
TmEnd = rstMyInv!SvcRest 'Assume end at this restoration
rstMyInv.MoveNext 'Move past first Record
'Check for Last Record as Single Interval
If (rstMyInv.EOF) Then
CummTime = CummTime + DateDiff(&quot;n&quot;, TmStrt, TmEnd)
End If

Do While Not rstMyInv.EOF

If (rstMyInv!dtoccur < TmEnd) Then
'Overlap, Check/Adjust End Time

If (rstMyInv!SvcRest > TmEnd) Then
'ADjust End of Interval
TmEnd = rstMyInv!SvcRest
End If

Else

'Close Outtage/Incident Report
CummTime = CummTime + DateDiff(&quot;n&quot;, TmStrt, TmEnd)
TmStrt = rstMyInv!dtoccur 'Start Time for Outtage
TmEnd = rstMyInv!SvcRest 'Assume end at this restoration

End If

rstMyInv.MoveNext 'Next Record
'Check for Last Record as Single Interval
If (rstMyInv.EOF) Then
CummTime = CummTime + DateDiff(&quot;n&quot;, TmStrt, TmEnd)
End If


Loop

Debug.Print &quot;Outtage for Invoice # &quot; & rstInv!InvNum & &quot; is = &quot; & CummTime

CummTime = 0
rstInv.MoveNext
Loop

End Function
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[padinka]

Thanks for the help. You are right, it is only a little piece, but it was the little piece that was giving me problems. Your code works great with very little modification. You're a gem. [sig]<p>Trisha<br><a href=mailtoadinka@yahoo.com>padinka@yahoo.com</a><br>[/sig]