x

Send email when total count is over 100

Hi,

I'm using the following query to pull report data. How do I modify this query so that I can send an email when the total count from this query is more than 100.

I will put this in a stored procedure than use the SQL Agent to run the task on a schedule.

 SELECT DISTINCT 
                       P.ID, P.DESCRIPTION, P.ABC_CODE,  P.QTY_ON_HAND, 
                       C.LAST_COUNT_DATE, C.COUNT_FREQ, DATEDIFF(day, C.LAST_COUNT_DATE, GETDATE()) AS NUM_DAYS, PT_LOCATION.LOCATION_ID, 
                       PT_LOCATION.WAREHOUSE_ID, PT_LOCATION.QTY
 FROM         PT_Main AS P INNER JOIN
                       C_COUNT_PART AS C ON P.ID = C.PART_ID INNER JOIN
                       PT_LOCATION ON P.ID = PT_LOCATION.PART_ID
 WHERE     (DATEDIFF(day, C.LAST_COUNT_DATE, GETDATE()) > C.COUNT_FREQ) AND (NOT (P.QTY_ON_HAND = '0'))
 ORDER BY P.ABC_CODE
more ▼

asked Jul 22, 2013 at 04:41 PM in Default

avatar image

technette
1.4k 100 114 121

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Please see these links:

  1. http://msdn.microsoft.com/en-us/library/ms190307.aspx

  2. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/58381629-8b16-4a8c-bf61-7928a030825f/send-email-with-tsql

It describes how to send emails using T-SQL Code.

FYI - You'll have to unable this option on your server first and then get the proper permissions. Everything is described in the links above. Please read link #1 first, to understand "How To" in link #2.

Hopefully that helpds!

more ▼

answered Jul 23, 2013 at 02:50 PM

avatar image

eaglescout
720 5 8 12

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x53
x14

asked: Jul 22, 2013 at 04:41 PM

Seen: 958 times

Last Updated: Jul 23, 2013 at 02:50 PM

Copyright 2018 Redgate Software. Privacy Policy