Need some help troubleshooting my first email alert trigger

Question

Need some help troubleshooting my first email alert trigger

Viewing 2 posts - 16 through 16 (of 16 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

something like this will work, if you still need to send one email for each @taker AND each Taker's commisionless order;

if you want to roll them you'd need to group by email address, and create a varialble to add all the order id's together.

SELECT DISTINCT

oe_hdr.order_no,

oe_hdr.customer_id,

oe_hdr.taker,

users.email_address,

invoice_hdr.bill2_name,

'Order #' + CONVERT(varchar,oe_hdr.order_no) + ' for ' + invoice_hdr.bill2_name + ' has a $0 commission cost, please correct' As Subject

INTO #TMP

FROM oe_hdr

INNER JOIN (SELECT order_no, min(commission_cost) as commission_cost from oe_line group by order_no) X

on oe_hdr.order_no=x.order_no

LEFT OUTER JOIN users

on oe_hdr.taker = users.id

LEFT OUTER JOIN invoice_hdr

on oe_hdr.customer_id=invoice_hdr.customer_id

WHERE x.commission_cost < 0.01

--invoices created within 40 days

AND oe_hdr.order_date > DATEADD(d,-40,getdate())

declare @Subject varchar(8000),

@email varchar(100)

@CurrentMessage varchar(50)

set @emails=''

SET @Subject=

declare c1 cursor for select email_address,CONVERT(varchar,order_no) + ' for ' + bill2_name As Msg FROM #TMP

open c1

fetch next from c1 into @email,@Subject

While @@fetch_status <> -1

begin

SET @Subject = 'Invoices that have been generated with a zero commission in the last 40 days:' + @Subject

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Alerts',

@Recipients = @email

@Subject = @Subject

end

close c1

deallocate c1

s.'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bjohnson-533065 SSC Eights! Points: 929 More actions · Answer 2

^ I ran the first part of this and it only returned 679 rows - much nicer than the 220,000 I first saw. 😀

One advantage I can see to this over a trigger is that if I set it up to run daily or even twice a day; the order takers that don't fix the problems will be constantly reminded about the same incorrect orders every day to the point where they will eventually get annoyed and fix their mistakes!