MTBI (Mean Time Between Incident) Date Difference Different Record

[mp80237]

I have a table with Platformscom.StartTime, Platformscom.EndTime,
Platformscom.ImpactSev. I need to find the MTBI from the ending
impact of once incident to the beginning impact of another incident.

For example I have one record with a StartTime of 10/29/2008 11:15:00
PM and an EndTime of 10/30/2008 12:33:49 AM. The next record has a
StartTime of 11/5/2008 10:14:00 PM and an EndTime of 11/5/2008
10:18:00 PM. I would like to get the time difference between
10/30/2008 12:33:49 AM and 11/5/2008 10:14:00 PM. I would like to do
that with each record.

Thank you so much for the help.

I had tried below, but the numbers were just subtracting the time in
the same record.

SELECT T.StartTime, (SELECT MAX(EndTime) FROM [qryPlatformscom] T1
WHERE T1.EndTime < T.EndTime) AS EndTime, DateDiff("n",
[StartTime],[EndTime]) AS [Interval]
FROM qryPlatformscom AS T
ORDER BY T.StartTime;

 

[John W. Vinson]

I have a table with Platformscom.StartTime, Platformscom.EndTime,
Platformscom.ImpactSev. I need to find the MTBI from the ending
impact of once incident to the beginning impact of another incident.

For example I have one record with a StartTime of 10/29/2008 11:15:00
PM and an EndTime of 10/30/2008 12:33:49 AM. The next record has a
StartTime of 11/5/2008 10:14:00 PM and an EndTime of 11/5/2008
10:18:00 PM. I would like to get the time difference between
10/30/2008 12:33:49 AM and 11/5/2008 10:14:00 PM. I would like to do
that with each record.

Thank you so much for the help.

I had tried below, but the numbers were just subtracting the time in
the same record.

SELECT T.StartTime, (SELECT MAX(EndTime) FROM [qryPlatformscom] T1
WHERE T1.EndTime < T.EndTime) AS EndTime, DateDiff("n",
[StartTime],[EndTime]) AS [Interval]
FROM qryPlatformscom AS T
ORDER BY T.StartTime;

You'll need a subquery to link each record to the next consecutive record.
Untested air code query here, but it should give you a start:

SELECT Avg(DateDiff("n", [A].[EndTime],
(SELECT Min(.[qryPlatformScom].[StartTime]) FROM [qryPlatformScom] AS B
WHERE B.StartTime > A.EndTime)))
AS MTBI
FROM [qryPlatformScom] AS A;

I'm not sure you can use a Subquery as an argument to a function - you might
need to use DMin() instead.

 

[mp80237]

I have a table with Platformscom.StartTime, Platformscom.EndTime,
Platformscom.ImpactSev.  I need to find the MTBI from the ending
impact of once incident to the beginning impact of another incident.

For example I have one record with a StartTime of 10/29/2008 11:15:00
PM and an EndTime of 10/30/2008 12:33:49 AM.  The next record has a
StartTime of 11/5/2008 10:14:00 PM and an EndTime of 11/5/2008
10:18:00 PM.  I would like to get the time difference between
10/30/2008 12:33:49 AM and 11/5/2008 10:14:00 PM.  I would like to do
that with each record.

Thank you so much for the help.

I had tried below, but the numbers were just subtracting the time in
the same record.

SELECT T.StartTime, (SELECT MAX(EndTime) FROM [qryPlatformscom] T1
     WHERE T1.EndTime < T.EndTime) AS EndTime, DateDiff("n",
[StartTime],[EndTime]) AS [Interval]
FROM qryPlatformscom AS T
ORDER BY T.StartTime;

You'll need a subquery to link each record to the next consecutive record..
Untested air code query here, but it should give you a start:

SELECT Avg(DateDiff("n", [A].[EndTime],
(SELECT Min(.[qryPlatformScom].[StartTime]) FROM [qryPlatformScom] AS B
WHERE B.StartTime > A.EndTime)))
AS MTBI
FROM [qryPlatformScom] AS A;

I'm not sure you can use a Subquery as an argument to a function - you might
need to use DMin() instead.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

It is giving me a error. I have tried with DMin and Min. I get the
following error when trying to run the query: Reserved error (-3087);
there is no message for this error. I can't even view it in the grid
view without error. Using query: SELECT Avg(DateDiff("n", [A].
[EndTime],
(SELECT Min(.[qryPlatformScom].[StartTime]) FROM [qryPlatformScom]
AS B
WHERE B.StartTime > A.EndTime)))
AS MTBI
FROM [qryPlatformScom] AS A;