The big difference between how you're counting the minutes
and how we are doing it (for the statistical logging) is that you will at a
status change at the time between the previous check and now to the new status
while we still add that to the previous status.
Example
check done at
3:00pm UP
check done at
3:15pm down
check done at
3:20pm up
You would then add 15 minutes to the DOWN (3:00 -> 3:15)
counter and 5 to the UP (3:15 -> 3:20)
While we do (for the statistical) 15 to
the up (3:00 -> 3:15) counter and 5 to the down (3:15 ->
3:20)
Question is ofcourse which one is
correct??
Dirk.
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Shook
Sent: Wednesday, February 01, 2006 3:59 PM
To: [email protected]
Subject: RE: [SA-list] Reporting in SA
Ok, that makes sense. I don't need any past months
currently (I reset the table data after running some recap reports the
first of every month), but I could see how that would be handy. Also, that would
solve the issue I currently have of removing old checks from the
table.
Michael D. Shook
Technical Analyst
[EMAIL PROTECTED]
863 668 4477 (work)
863 860 4070 (cell)
863 665 1261 (fax)
www.saddlecrk.com
Technical Analyst
[EMAIL PROTECTED]
863 668 4477 (work)
863 860 4070 (cell)
863 665 1261 (fax)
www.saddlecrk.com
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dirk Bulinckx
Sent: Wednesday, February 01, 2006 9:25 AM
To: [email protected]
Subject: RE: [SA-list] Reporting in SASo an UPDATE statement within some kind of CASE statement (to get the correct column depending on the status) and if the update fails then it's an INSERT statement.That way you keep a total up/down/... time, not per month.If you would want to do this per month then when there is a month change you have to make both an UPDATE and an INSERT and the "key" is not only [SAID][UniqueID] but [SAID][UNIQUEID][YEARMONTH]Right?Dirk.
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Shook
Sent: Wednesday, February 01, 2006 3:14 PM
To: [email protected]
Subject: RE: [SA-list] Reporting in SAI think what's needed is a new table. The stats table is very useful for what it does (track status changes). The new table gets updated every cycle. Here's the schema I use:CREATE TABLE [myDB].[SARunningTimes] (
[IDKey] [int] IDENTITY (1, 1) NOT NULL ,
[SAID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UniqueID] [numeric](18, 0) NOT NULL ,
[HostID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[S1Min] [float] NULL ,
[S2Min] [float] NULL ,
[S3Min] [float] NULL ,
[S4Min] [float] NULL ,
[S5Min] [float] NULL ,
[S6Min] [float] NULL ,
[S7Min] [float] NULL ,
[TotalMin] [float] NULL ,
[LastCheckDateTime] [datetime] NULL ,
[CurrentStatusCycles] [float] NULL ,
[PreviousCheckStatus] [int] NULL
) ON [PRIMARY]
GOMy trigger on every change to the interchange table:If an entry for [SAID][UniqueID] does not exist, then an initial row is created with all fields populated as expected, with the exceptions of [S5Min] and [TotalMin] set to 1. (that's so it doesn't crash my web page that calculates percentages)If an entry for [SAID][UniqueID] does exist then:1) The minutes between the [LastCheckDateTime] and Now() are added to the appropriate status minutes column (like [S5Min] ).2) The minutes from Step 1 are added to [TotalMin].3) If the Check Status is the same as [PreviousCheckStatus], then [CurrentStatusCycles] is incremented by 1, else [CurrentStatusCycles] is set to 1.4) the current status overwrites [PreviousCheckStatus].Michael D. Shook
Technical Analyst
[EMAIL PROTECTED]
863 668 4477 (work)
863 860 4070 (cell)
863 665 1261 (fax)
www.saddlecrk.com--------------------------------------
The information contained in this message is intended only for the use of the addressee. If the reader of this message is not the intended recipient or agent of the intended recipient, you are hereby notified that any dissemination, distribution, or copying of the message is strictly prohibited.
--------------------------------------
The information contained in this
message is intended only for the use of the addressee. If the reader of this
message is not the intended recipient or agent of the intended recipient, you
are hereby notified that any dissemination, distribution, or copying of the
message is strictly prohibited.
