MTBI (Mean Time Between Incident) Date Difference Different Record


M

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;
 
Ad

Advertisements

J

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.
 
Ad

Advertisements

M

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;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top