1. Overview
This document talks about how to send report with excel file automatically to Finance Team every month of 21st. This report for employee referred the candidate who will complete the 90 days and above for the current month of 21st.
2. Technologies and Tools Used
The following technologies has been used to automatically send report to Finance Team.
Ø MS SQL Server
Ø DotNet & Angular
3. Use Case
Assume that there is a requirement to automatically send report to Finance Team.
4. Architecture
Following steps explains in detail,
Step 1: First we have to check whether joined candidates reach 90 days for current month of 21st.
To select Current Year, Month , Date and calculate candidate cross 90 days.
DATEDIFF(DAY, DateofJoin, GETDATE())>=90 AND DATEDIFF (DAY, DateofJoin, GETDATE())<=Datedifference
BEGIN
DECLARE @DateDifference int=0;
DECLARE @LastMonth int=0;
DECLARE @CurrentYear int=0;
DECLARE @CurrentMonth int=0;
DECLARE @PreviousYear int=0;
SELECT @CurrentMonth =DATEPART(mm,DATEADD(mm,0,GETDATE());
SELECT @LastMonth =DATEPART(mm,DATEADD(mm,-1,GETDATE());
SELECT @CurrentYear =DATEPART(mm,DATEADD(yyyy,0,GETDATE());
set @PreviousYear=@CurrentYear;
if(@LastMonth=12)
BEGIN
SELECT @CurrentYear =DATEPART(mm,DATEADD(yyyy,-1,GETDATE());
END
DECLARE @FromDate varchar(10), @ToDate varchar(10);
set @FromDate=convert(varchar,@LastMonth) +’/’+’21’+’/’+convert(varchar,@PreviousYear);
set @ToDate=convert(varchar,@CurrentMonth) +’/’+’21’+’/’+convert(varchar,@CurrentYear);
END
Step 2: Query for Select the All joined employee who reach 90 days.
SELECT DATEDIFF(DAY, DateofJoin,GETDATE())>=90 AND DATEDIFF(DAY, DateofJoin,GETDATE())>=@DateDifference
Step 3: Attach Excel file with email.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Profile1’,
@recipients = @RecipientEmail,
@subject = @Subject,
@body = @Body,
@importance = ‘HIGH’,
@query_attachment_filename = ‘Referral_bonus.xls’,
@attach_query_result_as_file = 1,
@query_result_no_padding=1,
@query = @query,
@query_no_truncate = 1,
@query_result_header = 0,
@exclude_query_output = 0,
@append_query_error = 1,
@query_result_width = 32767,
@body_format =’HTML’
5. Conclusion
We can send email with excel attachment using stored procedure, to send automatically email with attachment. Schedule job and run.
