Advice Sought: How would you do this?

Question

Post reply

Advice Sought: How would you do this?

Phil Parkin

SSC Guru

Points: 246713
More actions
December 12, 2010 at 11:43 am

#244311

A client I am working with has asked me to provide the following feature:
Run a process periodically (probably every hour or so) which looks for 'new' records in a certain table ('new' means that the process has not previously identified them).
Each of these records will have an e-mail address associated with it (which is that of an internal member of staff) and a hyperlink (plus some other identifying information - 'Description' perhaps). The same e-mail address is likely to appear on multiple records. The hyperlink uniquely identifies the row and, when clicked, will open the record in the client's internal Web-based CRM system.
The client would like the process to send each identified user an e-mail listing their hyperlinks (so one e-mail per 'found' user, not one e-mail per record). The process will then set a 'processed' (or whatever) flag against each record that has had an e-mail sent, so that the same hyperlink is not repeatedly e-mailed every time the job runs.
I'm looking forward to others' ideas on how they would achieve this - before Wednesday, which is when I shall be developing it 🙂
Cheers
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

Phil,

So, something like this?

-- store off what you're doing so you can mark them as processed

DECLARE @TableNew TABLE (RowID INT IDENTITY, Col2 = PK of TABLE);

INSERT INTO @TableNew SELECT PK FROM TABLE WHERE Record IS NEW;

WITH CTE AS

(

-- get the unique email_id entries

SELECT email_id

FROM Table

JOIN @TableNew t ON t.Col2 = Table.PK -- so you only get the ones you're playing with

WHERE record IS NEW -- obviously need to change to your requirements)

GROUP BY email_id

ORDER BY email_id

)

-- create comma-separated list of hyperlinks per email_id

-- You can now send an email to each email_id for all new hyperlinks

SELECT email_id, STUFF((SELECT ',' + HyperLink FROM TABLE WHERE email_id = CTE.email_ID FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')

FROM CTE;

-- send emails...

-- mark them as processed

UPDATE T1

SET Processed = 1

FROM Table T1

JOIN @TableNew t2

ON t1.PK = t2.PK;

A better approach might be able to be developed upon seeing the table DDL, sample data, etc. You know the drill...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

TomThomson SSC Guru Points: 104773 More actions · Answer 2

One approach is to populate an auxiliary table using an "after insert" trigger, and then have a job that processes the auxiliary table (and depopulates it) at whatever frequency is required). Seems pretty straightforward.

Tom

Phil Parkin SSC Guru Points: 246713 More actions · Answer 3

Guys, I was looking for an old post of mine and came across this one, only to see that I never said thank you :blush:

So let me remedy that: thank you very much for the responses.

Phil

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.