Search the Community

Hi, I have a Procedures ready to auto send email to employee when the reminder date equal today. Let me show you what is inside my employee database. As you can see, there is two records for employee 1001. This is because this employee short notice for 2 months. [sdate] There is also a shortNotice which is in December is 4 day, in January is 18 days. So, the consist days is 18+4=22 Days This is my procedure code, it's working, this procedure will trigger out email if meet the criteria. ***REMEMBER TO CHANGE THE SERVER NAME & PORT NUMBER*** USE [RSA] GO /****** Object: StoredProcedure [dbo].[sp_send_cdosysmail] Script Date: 12/10/2015 3:55:00 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_send_cdosysmail] @From varchar(100)="notification@mail.com", @Subject varchar(100)="A Reminder for you", @body varchar(4000)=" ", @attachments varchar(4000)=NULL AS DECLARE @out_desc VARCHAR(1000), @out_mesg VARCHAR(10) DECLARE @empid nvarchar(255), @Name nvarchar(255), @Email nvarchar(255), @reminderDate datetime, @lastDay datetime, @shortNotice int, @consistsDays int, @sdate datetime Declare C1 CURSOR READ_ONLY FOR SELECT DISTINCT [empid],[Name],[Email],[reminderDate],[lastDay],[shortNotice],[consistsDays],[sdate] FROM [resigntblView] Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare @description varchar(500) Declare @output varchar(1000) Declare @files table(fileid int identity(1,1),[file] varchar(255)) OPEN C1 FETCH NEXT FROM C1 INTO @empid,@Name, @Email, @reminderDate, @lastDay,@shortNotice,@consistsDays,@sdate WHILE @@FETCH_STATUS = 0 BEGIN IF DATEPART(DAY,@reminderDate) = DATEPART(DAY,GETDATE()) AND DATEPART(MONTH,@reminderDate) = DATEPART(MONTH,GETDATE()) AND DATEPART(YEAR,@reminderDate) = DATEPART(YEAR,GETDATE()) BEGIN SET @body = '<b>Hi ' + @Name + '</b><br />This is a reminder' + '<br /><br />Thank You' --************* Create the CDO.Message Object ************************ EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT --***************Configuring the Message Object ****************** -- This is to configure a remote SMTP server. -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/sendusing"). Value','2' -- This is to configure the Server Name or IP address. -- Replace MailServerName by the name or IP of your SMTP Server. EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver"). Value', '#######' --PUT YOUR SERVER HERE -- Save the configurations to the message object. EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null -- Set the e-mail parameters. EXEC @hr = sp_OASetProperty @iMsg, 'To', @Email EXEC @hr = sp_OASetProperty @iMsg, 'From', @From EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @body EXEC @hr = sp_OAMethod @iMsg, 'Send' -- Do some error handling after each step if you need to. -- Clean up the objects created. EXEC @hr = sp_OADestroy @iMsg END FETCH NEXT FROM C1 INTO @empid,@Name, @Email, @reminderDate,@lastDay,@shortNotice,@consistsDays,@sdate END CLOSE C1 DEALLOCATE C1 Once the Procedures is execute, this is the OUTPUT that i received in my inbox: Hi Foo, Kelvin This is a reminder Thank You This Email will Trigger two times since there is a two column of data. My question is, how to merge some of the data in one, and send the email only once. Expected Output: Hi Foo, Kelvin, You are required to compensate USD 100 to the Company in view of your 22 days of short notice. The 1 month of your notice period consist of 22 working days, (01-12-2015 - 31-12-2015), 4 Days The 2 month of your notice period consist of 22 working days, (01-01-2016 - 31-01-2016), 18 Days Thank You. Thanks for your big help.