im trying to right an SQL Query that will do the following task:
SellDate = '2014-07-15'
ExpirationDate = '2015-07-15'
TodaysDate = '2015-06-15' --notify me by email
An item is sold, (SellDate) and is set to expire in 12 months (ExpirationDate)... 1 month before, I want SQL Server to email me informing that the item is about to expire based upon (TodaysDate)
so using the above 3 dates... could someone please give me some pointers as to where I go next... this is what I have tried so far.. the query below will find the item however I now want SQL Server Agent to send me an email for each row that is returned, not when nothing is returned
select * from Parts WHERE ExpirationDate = DATEADD(month, +1, '2015-06-15')
Best How To :
Maybe you can create a simple HTML based on the items you need to return and then send the email
Declare @html Varchar(max)
SET @html = CAST(( SELECT ColName1 AS 'td','',ColName2 AS 'td','',
ColName3 AS 'td' ,''
WHERE Convert(date,ExpirationDate) = DATEADD(month, +1, getdate()) //Convert to date since not sure whether the datatype is date or datetime
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
Then you can inert this part (@html) in to proper format
And using sp_send_dbmail
@recipients = @Emails , --add required emails here, use ; between email address if multiple
@body_format = 'HTML',
@importance = 'High',
@body = @html,
@subject = @subject -- add subject here