December 12, 2010 at 11:43 am
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 12, 2010 at 12:25 pm
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
December 12, 2010 at 7:28 pm
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
February 17, 2011 at 12:28 pm
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply