Subtracting date and time values

[Tom Ellison]

Dear Calvin:

At first, I didn't think you were following me. I hope the recap
helps. I thought I'd wait to see how you do with this information.
Please let me know how it works out.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I'll check that out and get back wtih you...Thanks again,
Tom. Any other thoughts from my previous message? I
hope that we're on the "same page".

CALVIN

-----Original Message-----
I understand what you are saying, except that the
document I'd listed earlier is the way it appears in the
database. Therefore, DateClosed does start physically
with 10/14/99 10:39 AM. It is not Null. However, I see
what you are trying to do, I think. From what you're
listing, given the query, I should be able to produce the
MTBF.

Therefore, please correct me if I'm wrong...your previous
query can arrange things such that I can produce MTBF
(without changing the contents of either DateClosed and
DateCreated)...is that correct? Is it query that does
things behind the scenes?

Then, I can produce a query, such as:

SELECT [TBL-AssetRecord].DateCreated, [TBL-

AssetRecord].DateClosed,
Datediff("n",[DateCreated],[DateClosed]/60 AS MTBF
FROM [TBL-AssetRecord]
GROUP BY [TBL-AssetRecord].DateCreated,[TBL-
AssetRecord].DateClosed
ORDER BY [TBL-AssetRecord].DateCreated
CALVIN
-----Original Message-----
Dear Calvin:

The query I sent is supposed to show the DateCreated with the previous
DateClosed. Isn't that what it is giving you? I was expecting:

DateCreated DateClosed
10/7/99 10:24 AM NULL
10/14/99 1:44 PM 10/14/99 10:39 AM
10/15/99 7:06 AM 10/14/99 1:47 PM
10/16/99 7:14 AM 10/16/99 6:30 AM
10/17/99 6:37 AM 10/16/99 7:39 AM

The DateClosed column should be the ones from the previous row. Based
on this query, you can calculate the MTBF. Right?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 25 Mar 2004 15:06:38 -0800, "Calvin Shane"

We're getting closer. MTBF is not getting the difference
from one DateClosed, but the previous DateClosed, so that
in my example:

DateCreated DateClosed MTBF MTTR
10/7/99 10:24 AM 10/14/99 10:39 AM n/a 168.25
10/14/99 1:44 PM 10/14/99 1:47 PM 3.08 0.05
10/15/99 7:06 AM 10/16/99 6:30 AM 17.32 23.40
10/16/99 7:14 AM 10/16/99 7:39 AM 0.73 0.43
10/17/99 6:37 AM 10/18/99 8:18 AM 22.97 25.68

The difference betw. 1:44pm and 10:39am is 3hours, 5mins
(Thus 3.08 hours)
The difference betw. 7:06am (on 15th) and 1:47pm (on
14th) is 17hours 19mins (Thus 17.32 hours) and so on.

CALVIN

.

.

 

[Calvin Shane]

Thanks again, Tom. The MTBF does work with your query.
One quick question, if I'm also trying to produce Mean
Time To Repair, which uses the DateClosed minus
DateOpened from the same row of data, given what your
query has done for MTBF, what can or should I adjust in
your query to make it work for both (I currently have two
separate queries now)? Is there a manual or document
that you've used that would help me to learn more about
this?

CALVIN

 

[Tom Ellison]

Dear Calvin:

Your query can alias the DateClosed from the previous row as
PreviousDateClosed. You can then add the DateClosed from the current
row as well, and calculate on both of them in the same query.

It is hard to say how I learned to write queries. I got some from
books, but none of them was the magic bullet for me. I learned some
on line, got some expert help in a newsgroup once or twice. But
mostly, 4GL came to me naturally. Getting a feel for non-procedural
language is a bit key, in my opinion. You're describing the details
about what you want done, mostly without being concerned with the
sequence of events.

I'm the wrong guy to ask. Probably most people's learning experience
is quite different. But I would recommend this: go to the big city,
get to a bookstore with a large technical section, and start finding
books that address your concerns at a level you can readily absorb.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Vinson]

Thanks again, Tom. The MTBF does work with your query.
One quick question, if I'm also trying to produce Mean
Time To Repair

Just hope you don't end up in the situation described in the poem by
the prolific writer Anonymous:

An IBM field man named Blair
Cried out from the depths of despair,
"To fix won't avail
For the mean time to fail
Is less than the time to repair!"

 

[Calvin Shane]

Tom, thanks. I am now working on combining both the MTTR
(which just uses DateClosed minus DateOpened) and the MTBF.

Given the query that you've given me to this point, is
there a way to perform MTTR, and MTBF? I've done both
queries separately, but unable to together, given your
query. It seems to do one, or the other, but not both.
Can I do MTTR first, then use your SQL query that adjusts
the data as you'd listed, then perform MTBF. I've tried
to combine them both, with no success to this point.

CALVIN

 

[Tom Ellison]

Dear Calvin:

The only trick would be to keep track of the DateClosed from the same
row as DateCreated, and the PrevDateClosed using the subquery.

Two approaches are viable. One is to have a lower level query that
returns DateCreated, DateClosed, and PrevDateClosed, then write
another query that gives MTBF and MTTR from those numbers. It is also
possible to re-use the subquery in the MTBF calculation and do it all
in one query. But this is more complex.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts