Example: Using Event Data High Values to Prevent Duplicate Notifications

The following example shows how to use an event chronicle table to prevent duplicate notifications based on the highest values for event data during a specified time period. In this example, a stock notification application provides you with notifications when their selected stocks cross predefined thresholds.

Scenario

The application gets new stock event data every 20 minutes from a Web service. The data for the morning is shown in the following table.

Your own event-driven subscription is for a notification when AWKS stock reaches a value of $71.00 (US dollars) or greater. You therefore receive a notification based on the 10:00 GMT data. After the event batch is processed and your notification is generated, the high value for the day so far, $71.55, is entered or updated in the event chronicle table by an event chronicle rule that is defined for this application.

Subscription Rule

The 10:20 GMT event data arrives for processing. The notification generation action defined for the subscription rule uses the event chronicle table to prevent duplicate notifications. It does this by excluding duplicate notifications once the trigger price has been passed, unless a new high has been reached:

-- Generate notifications 
SELECT dbo.StockNotificationNotify(S.SubscriberId,
    S.SubscriberDeviceName, S.SubscriberLocale,
    E.StockSymbol, E.StockPrice)
FROM dbo.StockSubscriptions S 
    JOIN dbo.StockEvents E
        ON S.StockSymbol = E.StockSymbol
    JOIN dbo.StockEventChron C
        ON S.StockSymbol = C.StockSymbol
WHERE S.StockTriggerPrice <= E.StockPrice
    AND S.StockTriggerPrice > C.StockHighPrice

-- Insert new events into event chronicle
INSERT dbo.StockEventChron(StockSymbol, StockHighPrice)
SELECT E.StockSymbol, E.StockPrice
FROM dbo.StockEvents E
WHERE E.StockSymbol
    NOT IN (SELECT StockSymbol FROM dbo.StockEventChron)

-- Update existing events in event chronicle
UPDATE dbo.StockEventChron
SET C.StockHighPrice = E.StockPrice
FROM dbo.StockEvents E JOIN dbo.StockEventChron C
ON E.StockSymbol = C.StockSymbol
WHERE E.StockPrice > C.StockHighPrice

Results

Any subscription with a trigger price of $71.55 or lower has already received a notification, and does not receive another. Any subscription that has a trigger price greater than the stored high price of $71.55, but less than or equal to the new high price of $72.00, receives a notification. After notifications are generated, the event chronicle rule again updates the event chronicle table with the latest data.

See Also

Concepts

Defining Chronicles for an Event Class
Defining Event Chronicle Tables
Defining Subscription Rules
Example: Using an Event Chronicle for Scheduled Subscriptions
Example: Comparing Event Data to Prevent Duplicate Notifications

Help and Information

Getting SQL Server 2005 Assistance