Help with writing Triggers

I have no experience with Triggers and need help to create 2. My goal is to
generate the average and Stard Devation for 2 values when they are inserted
or updated. In the first field I insert a count that I need to calculate th
e
mean and StdDev based on the values of the past 30 days. The other is the
same with the addition of the past 30 days based on wdays or wends.
I currently am doing this with a batch but it would be simpler to manage if
the rows values were calculated when the values are inserted or updated.
Below are the table design and the batch statement.
-- ========================================
===============================
CREATE TABLE [dbo].[RollingRecordCount] (
[CDATE] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NCOUNT] [bigint] NOT NULL ,
[RCOUNT] [bigint] NOT NULL ,
[IsWday] AS (convert(char(1),case when ((datepart(wday,[CDATE]) = 7
or datepart(wday,[CDATE]) = 1)) then 'N' else 'Y' end)) ,
[MeanNc] [float] NULL ,
[StdDevNc] [float] NULL ,
[MeanRc] [float] NULL ,
[StdDevRc] [float] NULL ,
[InsertModDate] [datetime] NULL
) ON [PRIMARY]
GO
========================================
====================================
======
UPDATE RollingRecordCount
SET MeanNc = (SELECT AVG(NCOUNT)
FROM RollingRecordCount
WHERE CDATE BETWEEN (@.startdate) AND (@.enddate)),
StdDevNc = (SELECT STDEVP(NCOUNT)
FROM RollingRecordCount
WHERE CDATE BETWEEN (@.startdate) AND (@.enddate)),
MeanRc = CASE
WHEN IsWday = 'Y'
THEN (SELECT AVG(RCOUNT)
FROM RollingRecordCount
WHERE ISWday = 'Y' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
ELSE (SELECT AVG(RCOUNT)
FROM RollingRecordCount
WHERE ISWday = 'N' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
END,
StdDevRc = CASE
WHEN IsWday = 'Y'
THEN(SELECT STDEVP(RCOUNT)
FROM RollingRecordCount
WHERE IsWday = 'Y' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
ELSE (SELECT STDEVP(RCOUNT)
FROM RollingRecordCount
WHERE IsWday = 'N' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
END
FROM RollingRecordCount
WHERE CDATE = @.enddateJim Abel (JimAbel@.discussions.microsoft.com) writes:
> I have no experience with Triggers and need help to create 2. My goal
> is to generate the average and Stard Devation for 2 values when they are
> inserted or updated. In the first field I insert a count that I need to
> calculate the mean and StdDev based on the values of the past 30 days.
> The other is the same with the addition of the past 30 days based on
> wdays or wends. I currently am doing this with a batch but it
> would be simpler to manage if the rows values were calculated when the
> values are inserted or updated. Below are the table design and the batch
> statement.
Hm, I'm not that this is good for a trigger. As I understand it,
you want the values to reflect the last 30 days. But what if nothing
happens during a day? The values should still change, shouldn't they?
Of course, it may be a fair assumption that data is inserted everyday.
But how often? Recalculating everytime may be expensive?
(Basically, I say this, because I'm just about to leave, and don't
have the time to compose a trigger right now.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I agree with Erland. A view could be used here. But it really is difficult t
o
be 100% sure without seeing the DDL and some sample data.
ML
http://milambda.blogspot.com/|||Sorry for the lack of details with this request. Here is some more
information. The 2 fields NCOUNT and RCOUNT are inserted once esch day. On
rare ocasions the counts that are entered had been calculated incorrectly at
the datasource and I need to manually edit the particular row and change the
value for one or both counts and then recalculate the means and StdDev of
that row based on the previous 30 days of that rows date. The other message
suggested using a view and that may work as well. I'm just trying to develo
p
something that takes as little management as possible, the goal being that I
need only to enter the NCOUNT and/or the RCOUNT and the mean and StdDev
columns can be autimatically generated without me needing to pull up a batch
script.
"Erland Sommarskog" wrote:

> Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Hm, I'm not that this is good for a trigger. As I understand it,
> you want the values to reflect the last 30 days. But what if nothing
> happens during a day? The values should still change, shouldn't they?
> Of course, it may be a fair assumption that data is inserted everyday.
> But how often? Recalculating everytime may be expensive?
> (Basically, I say this, because I'm just about to leave, and don't
> have the time to compose a trigger right now.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||I added some more detail to Erlands message in a reply. I thought that the
script under my 2nd ====== line was the ddl of the existing batch less the
declaration of the Start and en date parameters. the end date is set by
getting the Max date after the dauly insert occurs and the start date is 30
days less. In the case of a manual update to a previously entered row the
end date is the CDATE value and the Start date is 30 days less.
If I am ubderstanding your suggestion to use a view the mean and StdDev
fields would be calculated in the view design and they would not be necessar
y
in the underlyung table, is this chat you're suggesting?
"ML" wrote:

> I agree with Erland. A view could be used here. But it really is difficult
to
> be 100% sure without seeing the DDL and some sample data.
>
> ML
> --
> http://milambda.blogspot.com/|||Exactly. But to be certain we'd have to see more DDL (table definition) and
sample data.
ML
http://milambda.blogspot.com/|||Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Sorry for the lack of details with this request. Here is some more
> information. The 2 fields NCOUNT and RCOUNT are inserted once esch day.
> On rare ocasions the counts that are entered had been calculated
> incorrectly at the datasource and I need to manually edit the particular
> row and change the value for one or both counts and then recalculate the
> means and StdDev of that row based on the previous 30 days of that rows
> date. The other message suggested using a view and that may work as
> well. I'm just trying to develop something that takes as little
> management as possible, the goal being that I need only to enter the
> NCOUNT and/or the RCOUNT and the mean and StdDev columns can be
> autimatically generated without me needing to pull up a batch script.
If rows are inserted once per day, it makes more sense. I assume then
that CDATE is the primary key in RollingRecordCount? Wittout a primary
key, it gets difficult.
Below is a trigger. Some remarks: I've replaced the sub-selects with
joins to a derived table. This is a proprietary syntax and not very
portable. On the other hand, on SQL Server this syntax usuaally gives
better performance. I did not include the computation of StdDevRc, but
left that as an exercise. :-) You can use MeanRc as a pattern. I also
added 1E0* in some places to force a conversion to float. It's meaningless
to store the means as float, if the result is integer only. (Which it is
if you say AVG(NCOUNT) without any conversion.
CREATE TRIGGER rolling_tri FOR INSERT, UPDATE AS
UPDATE RollingRecordCount
SET MeanNc = R1.MeanNc,
StdDevNc = R1.StdDevNc,
MeanRc = CASE IsWday
WHEN 'Y' THEN R1.MeanWDay
ELSE R1.MeanWEnd
END
FROM RollingRecordCount R
JOIN (SELECT i.CDATE, MeanNc = AVG(1E0 * R.NCOUNT),
STDEVP(1E0 * R.NCOUNT
MeanWDay = SUM(1E0 * R.RCOUNT) /
SUM(CASE R.ISWday WHEN 'Y' THEN 1 ELSE 0 END),
MeanWEnd = SUM(1E0 * R.RCOUNT) /
SUM(CASE R.ISWday WHEN 'N' THEN 1 ELSE 0 END)
FROM inserted i
JOIN RolleingRecordCount ON R.CDATE
BETWEEN i.CDATE - 30 AND i.CDATE
GROUP BY i.CDATE) AS R1 ON R.CDate = R1.CDate
In lieu of sample data, the code is untested.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx