RE: [SA-list] Reporting in SA

[Michael Shook]

Title: Message

I 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]
GO

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