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.
| Time in | Stock symbol | Stock price ($USD) |
|---|---|---|
09:00 GMT |
AWKS |
69.98 |
09:20 GMT |
AWKS |
70.35 |
09:40 GMT |
AWKS |
70.87 |
10:00 GMT |
AWKS |
71.55 |
10:20 GMT |
AWKS |
72.00 |
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
Important
If you are defining an application in an XML file, you must replace reserved XML characters, such as '>', with their entity references. For more information, see XML Reserved Characters.
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