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 weekdays or weekends.
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 ,
    [IsWeekday] AS (convert(char(1),case when ((datepart(weekday,[CDATE]) = 7
or datepart(weekday,[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

Jim Abel (JimA***@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.)

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/

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 necessary
in the underlyung table, is this chat you're suggesting?

Show quoteHide quote

Exactly. But to be certain we'd have to see more DDL (table definition) and
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 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.

Show quoteHide quote

Jim Abel (JimA***@discussions.microsoft.com) writes:
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 IsWeekday
                          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.ISWeekday WHEN 'Y' THEN 1 ELSE 0 END),
                  MeanWEnd = SUM(1E0 * R.RCOUNT) /
                           SUM(CASE R.ISWeekday 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.

Why query optimizer does not select the index?
Popuate field with value from last row
Finding the right record
CHARINDEX in CASE Within SELECT Statement
Default Value Calculation