So 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 SA
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
[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
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.
