RE: Search Logic Challenge -

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Jeff Waskiewicz (JeffWaskiewicz_at_discussions.microsoft.com)
Date: 12/10/04 

Date: Fri, 10 Dec 2004 12:41:01 -0800

I may be completely off my rocker as it's Friday at 3:15PM but I have looked
at your post a few times and I keep coming back to the same thought. Don't
do the calculations in VB. Since the data is already being logged to SQL
Server why not create a couple of Triggers/Stored Procedures in SQL. From a
1000 miles up it would look someting like this...

Create a New Table called Downtime with two Columns Date and Downtime
Create a trigger that fires everytime an enable event occurs fires a Stored
Procedure

---- Stored Procedure PseudoCode

If StartTime > 07:00 Then
Query Last End Time
If LastEndTime = Yesterday
Downtime = DateDiff (StartTime - 07:00)
Else
Downtime = DateDiff (StartTime - EndTime)
End If
Get PreviousDownTime
DownTime = Downtime + PreviousDownTime
UpdateRecord Downtime
Else
WriteRecord Date and 0
End If
-----

Then you could just read the data out of the downtime table and report it as
you need. I work mainly in Manufacturing/Process Control and multiple
customers do this sort of things to track equipment down times. In my mind
it's seems a lot easier to do the calculations event based and roll them up
ratther than trying to back calculate all the a data.

"DavidM" wrote:

> Hello - I have a problem that I'm trying to address in VB6 and I'm
> soliciting feedback from others to determine the best algorithm to use.
>
> We currently have an online application that must be "enabled" at start of
> day so users can access application. At end of day, the application is
> "disabled".
>
> If there are any problems throughout the day, the application can be
> disabled/enabled as many times as necessary.
>
> The online is expected to be up at 7:00 AM every day until 08:00 PM.
>
> If the application is disabled (down) and re-enabled (up) during the day, I
> have to compute the number of minutes it was offline.
>
> When we determine downtime, we always look at yesterday, since it was the
> last full processing day and we haven't completed today yet.
>
> The "Enable" and "Disable" times are within our SQL Server database which is
> captured directly from mainframe.
>
> To compound my problem, we only compute downtime between Monday and Friday.
> Any weekend downtime (Saturday and Sunday) is considered Friday's date.
>
> Therefore, every day, Monday - Friday, I run a program that looks for
> downtime. On Monday, I always use a start date of Friday and an end date of
> Sunday to search for downtime. On Tuesday - Friday morning, I always use
> the day before. For Example, on Tuesday, I will use Monday's date. On
> Wednesday, I will use Tuesday's date. On Friday, I will use Thursday's
> date.
>
> My search logic has to take into account the multiple days that may be
> involved due to weekend. Otherwise, it's basically within 24 hour period of
> prior day.
>
> Since I'm rewriting an old program we have which there is no source any
> longer - I have to reverse engineer how they determine total minutes down
> per day.
>
> There is one assumption within the program -- it always starts it's search
> looking for "disable" times 2 days back instead of one. If it cannot find
> any "disables" from last 48 hours, it assumes application was "disabled" at
> midnight the day before.
>
> Here is a sample of what a query for a date of 12/07/04.
>
> Query for "disable" times:
> 12-06-04 20:21:26
> 12-07-04 17:29:00
>
> Query for "enable" times:
> 12-07-04 06:31:38
>
> In this particular instance, the online is expected to be up at 7:00 AM
> until 08:15 PM daily.
>
> There was no downtime as application was "enabled" prior to expected uptime
> at 06:31 and "disabled" after expected downtime of 20:21.
>
> Another example where there is downtime:
>
> Query for "disable times:
> 12-06-04 20:18:46
> 12-07-04 17:27:41
> 12-07-04 17:46:04
> 12-07-04 20:08:02
>
> Query for "enable" times:
> 12-07-04 06:33:57
> 12-07-04 17:30:14
> 12-07-04 18:04:01
>
> In this instance, the online is expected to be up at 07:00 AM until 08:00 PM
> daily.
>
> There was downtime as there was multiple disables/enables during the day of
> 12-07. For example:
>
> Application was enabled prior to expected time of 07:00 AM at 06:33:57. No
> downtime.
> System was disabled at 17:27:41 and enabled at 17:30:14 which resulted in 2
> mins (roughly) of downtime.
> It was again disabled at 17:46:04 and enabled at 18:04:01 which resulted in
> 17 mins( roughly) of downtime
> The application was finally disabled for day at 20:08:02 which was after the
> expected downtime of 08:00 PM.
> Therefore, there was a total of 19 minutes of downtime for 12-07.
>
> The application has to account for the multiple records that may be returned
> from disable/enables in addition to the special logic that must be performed
> on Monday morning to account for Friday - Sunday downtime.
>
> If anyone would like to take a stab at this... I'd be most interested to see
> what you have to say. I'm banging my head on this one.
>
> Thanks for your help.
>
>
>

Relevant Pages

  • RE: Search Logic Challenge -
    ... Create a New Table called Downtime with two Columns Date and Downtime ... I work mainly in Manufacturing/Process Control and multiple ... > There was downtime as there was multiple disables/enables during the day of ... > The application has to account for the multiple records that may be returned ...
    (microsoft.public.vb.database)
  • RE: Search Logic Challenge -
    ... Create a New Table called Downtime with two Columns Date and Downtime ... I work mainly in Manufacturing/Process Control and multiple ... > There was downtime as there was multiple disables/enables during the day of ... > The application has to account for the multiple records that may be returned ...
    (microsoft.public.vb.database.ado)