microsoft.public.sqlserver.notificationsvcs
[Top] [All Lists]

Re: Inconsistent Double-Ups out of the Generator

Subject: Re: Inconsistent Double-Ups out of the Generator
From: "Jason Liddiard"<>
Date: Tue, 4 Oct 2005 05:00:47 -0500
Newsgroups: microsoft.public.sqlserver.notificationsvcs
Apologies for my last post, I had copied the query from QA and while testing 
had removed the call to the Notify function. 

What I have is sales data in a table on a remote server. I have a scheduled 
SQL event provider that run everyday at 6am to collect all the WWID numbers 
( Employee ID's) for the days sales. An employee may have several rows of 
data with no unique ID for each row, so I collect only the Distinct WWID 
numbers. I add this to the events chronicle table, SaleAddedLog along with 
the time it was added. 

The scheduled rule is set to run at 10am. This should look at all the WWID 
numbers collected, JOIN with the Sales data, Subscriptions and Subscriptions 
Chron table to produce only notifications for sales for today's date hence 
the where clause testing for events greater than the last time subs were 
processed and events must be todays date. The where clause is meant to 
prevent doubleups should notifications not be processed on any day, as the 
next day, two wwid numbers would cause multiple duplications on the join. 

Not the most graceful solution I am sure, but with Unique ID's for each row 
of sales data, I could not think of an alternative solution. Any suggestions 
would be gratefully appreciated. 


<SubscriptionClasses>
  <SubscriptionClass>
  <SubscriptionClassName>NewSaleByText</SubscriptionClassName>
 <Schema>
  <Field>
   <FieldName>DeviceName</FieldName>
   <FieldType>NVARCHAR(255)</FieldType>
   <FieldTypeMods>not null</FieldTypeMods>
  </Field>
  <Field>
   <FieldName>Locale</FieldName>
   <FieldType>NVARCHAR(10)</FieldType>
   <FieldTypeMods>NOT NULL</FieldTypeMods>
  </Field>
  <Field>
   <FieldName>WWID</FieldName>
   <FieldType>INT</FieldType>
   <FieldTypeMods>NOT NULL</FieldTypeMods>
  </Field>
 </Schema>
 
<ScheduledRules>
 <ScheduledRule>
  <RuleName>MatchSalesForText</RuleName>
  <Action>
 --Insert rows into Processing Times chronicle 
 --for new subscriptions.
 INSERT  INTO NewSaleByTextSubscriptionProcessingTimes 
 (SubscriptionId, LastProcessingTime)
 SELECT  subscriptions.SubscriptionId, subscriptions.Created
 FROM    NewSaleByText subscriptions  
 WHERE   NOT EXISTS (
    SELECT  chron.SubscriptionId
    FROM    NewSaleByTextSubscriptionProcessingTimes chron
    WHERE   chron.SubscriptionId = subscriptions.SubscriptionId
                        )

 --Do the match.
 SELECT DISTINCT dbo.NewSaleSMSDeliveryNotify(
                subscriptions.SubscriberId, 
                subscriptions.DeviceName, 
                subscriptions.Locale, 
                sales.CustomerName,
                sales.PostCode,
                sales.SalesUnits,
                sales.PackageDescription)
FROM SaleAddedLog eventlog
JOIN JACGBHSERV01.CSL.dbo.vwNS_DailySales sales ON eventlog.WWID = sales.
WWID COLLATE Latin1_General_CI_AS
JOIN NewSaleByText subscriptions ON subscriptions.WWID = sales.WWID
JOIN NewSaleByTextSubscriptionProcessingTimes chron ON subscriptions.
SubscriptionId = chron.SubscriptionId
WHERE   eventlog.TimeAdded &gt;= chron.LastProcessingTime AND DATEPART(dd,
eventlog.TimeAdded) = DATEPART(dd,GETDATE())

--Update Processing Time Chronicle for those 
--subscriptions just processed.
UPDATE  NewSaleByTextSubscriptionProcessingTimes
SET     LastProcessingTime = GETUTCDATE()
FROM    NewSaleByTextSubscriptionProcessingTimes chron
JOIN NewSaleByText subscriptions ON chron.SubscriptionId = subscriptions.
SubscriptionId

--Clear down the SaleAddedLog (the Event Chronicle table)
DELETE FROM SaleAddedLog
WHERE DATEPART(dd,TimeAdded) &lt; DATEPART(dd,GETDATE()) 
  </Action>
 </ScheduledRule>
</ScheduledRules>

<Prev in Thread] Current Thread [Next in Thread>