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: Joe Webb
Date: Tue, 04 Oct 2005 05:48:44 -0500
Newsgroups: microsoft.public.sqlserver.notificationsvcs
Hi Jason - 

Thanks for posting the additional information and description of what
you're doing. That helps a lot. 

Is there a reason you're using GETDATE() in the WHERE clause rather
than GETUTCDATE()? 

Are you Digest Delivery?

-- 
Joe Webb
SQL Server MVP
http://www.sqlns.com


~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)



On Tue, 4 Oct 2005 05:00:47 -0500, "Jason Liddiard"<[email protected]>
wrote:

>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>