Prev: pivot chart legend shows "Sum of"
Next: help with a query
From: dietmarhannam on 30 Jun 2008 07:13
I am having a problem working out the logic for sorting out a date
series that includes times as well.
I have some data that looks at computer uptime, and I only want to see
how long the server is off for when rebooted to work out the total
time over the year.

Here's some of the data I have:

07/02/2008 09:52:22
07/02/2008 09:56:10
11/02/2008 19:59:54
11/02/2008 20:03:43
12/02/2008 14:43:57
12/02/2008 14:47:53
22/02/2008 09:47:46
22/02/2008 09:51:51
12/03/2008 19:29:51
12/03/2008 19:33:59

As you can see, all server reboots usually happen within the same day.
What I am after is a way to select a date (say 11/02/2008 for the
example) and then subtract the time it was rebooted (19:59:54) from
the time it was back up and running again (20:03:43) to get a time in
mins that the computer was unavailable. This would then need to be
done throughout the list.

Any ideas of where I should start? I have some experience with vb but
most of the work I have done is within Access itself.

Dietmar
From: Allen Browne on 30 Jun 2008 08:13
Assuming the date and time are in a Date/Time field named LogDT, use a
subquery to get the previous reboot time from the same table. Then use
DateDiff() to calculate the difference in minutes.

Something like this:

SELECT DateDiff("n", [LogDT]
(SELECT Max(LogDT) As LastBoot
FROM Table1 AS Dupe
WHERE Dupe.LogDT < Table1.LogDT)
AS MinutesSinceLastReboot
FROM Table1;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html#AnotherRecord

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<dietmarhannam(a)aol.com> wrote in message
news:615e8eb6-31f1-432d-99b5-f33c059ca955(a)a70g2000hsh.googlegroups.com...
>I am having a problem working out the logic for sorting out a date
> series that includes times as well.
> I have some data that looks at computer uptime, and I only want to see
> how long the server is off for when rebooted to work out the total
> time over the year.
>
> Here's some of the data I have:
>
> 07/02/2008 09:52:22
> 07/02/2008 09:56:10
> 11/02/2008 19:59:54
> 11/02/2008 20:03:43
> 12/02/2008 14:43:57
> 12/02/2008 14:47:53
> 22/02/2008 09:47:46
> 22/02/2008 09:51:51
> 12/03/2008 19:29:51
> 12/03/2008 19:33:59
>
> As you can see, all server reboots usually happen within the same day.
> What I am after is a way to select a date (say 11/02/2008 for the
> example) and then subtract the time it was rebooted (19:59:54) from
> the time it was back up and running again (20:03:43) to get a time in
> mins that the computer was unavailable. This would then need to be
> done throughout the list.
>
> Any ideas of where I should start? I have some experience with vb but
> most of the work I have done is within Access itself.
>
> Dietmar

 | 
Pages: 1
Prev: pivot chart legend shows "Sum of"
Next: help with a query