March 25, 2013 at 1:45 pm
Greetings,
I would like to send a daily email that shows in HTML the changes of our staff from the previous day. We have a table called EmployeeHistory that includes records for changes to our Employee tables with the date of the change along with the EmpId, Name, etc.
I have not done much work with HTML but I have the following code that selects records that have changed into a temp table called #History and then formats it into HTML and sends the email using sp_send_dbmail:
--
Select
Curr.EmpID as CurrEmpID,
Curr.FirstName as CurrFirstName,
Curr.LastName as CurrLastName,
Curr.JobTitle as CurrJobTitle,
Curr.DivisionID as CurrDivisionID,
Curr.DepartmentID as CurrDepartmentID
Curr.DateChanged as CurrDateChanged,
Prev.EmpID as PrevEmpID,
Prev.FirstName as PrevFirstName,
Prev.LastName as PrevLastName,
Prev.JobTitle as PrevJobTitle,
Prev.DivisionID as PrevDivisionID,
Prev.DepartmentID as PrevDepartmentID,
Prev.DateChanged as PrevDateChanged
into #History
from EmployeeHistory as Curr
join EmployeeHistory as Prev
on Curr.EmpID = Prev.EmpID AND
DATEDIFF(day,Prev.DateChanged, Curr.DateChanged) = 1 AND
(
Curr.FirstName <> Prev.FirstName or
Curr.LastName <> Prev.LastName or
Curr.JobTitle <> Prev.JobTitle or
Curr.DivisionID <> Prev.DivisionID or
Curr.DepartmentID <> Prev.DepartmentID
)
order by Curr.LastName, Curr.FirstName
DECLARE @HTML NVARCHAR(MAX);
SET @HTML =
N'<H1>Staff Data changes</H1>' +
N'<table border="1">' +
N'<tr><th>Date Changed</th><th>EmpId</th>' +
N'<th>Last Name</th><th>First Name</th>' +
N'<th>Job Title</th><th>Division</th><th>Department</th>' +
CAST ( (
SELECT
td = CurrDateChanged, '',
td = CurrEmpID, '',
td = CurrLastName, '',
td = CurrFirstName, '',
td = CurrJobTitle, '',
td = CurrDivisionID, '',
td = CurrDepartmentID, '',
td = PrevDateChanged, '',
td = PrevEmpID, '',
td = PrevLastName, '',
td = PrevFirstName, '',
td = PrevJobTitle, '',
td = PrevDivisionID, '',
td = PrevDepartmentID, ''
from #History
order by CurrLastName, CurrFirstName
for XML PATH('tr'), TYPE
) as NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Gmail',
@recipients = 'XXXX',
@subject = 'Staff Data changes',
@body = @HTML,
@body_format = 'HTML'
--
For each employee that has changed since yesterday, I would like the current data formated onto one row of the HTML and the previous data formatted onto a second row.
What is the syntax needed to separate this data into two rows? Thank you for your help.
gmrose
March 25, 2013 at 3:40 pm
I am hardly and expert on XML path and HTML, novice is more like it, but I have used similar method to send HTML emails.
you will need to split up your current and previous into different XML statements. your XML path is creating the rows so if you combine your current and previous within the same XML path they will be in the same row.
something like this might work (not tested)
DECLARE @HTMLcur NVARCHAR(MAX),
@HTMLprev NVARCHAR(MAX),
@body NVARCHAR(MAX);
set @body =
N'<H1>Staff Data changes</H1>' +
N'<table border="1">' +
N'<tr><th>Date Changed</th><th>EmpId</th>' +
N'<th>Last Name</th><th>First Name</th>' +
N'<th>Job Title</th><th>Division</th><th>Department</th>'
SET @HTMLcur =
CAST ( (
SELECT
td = CurrDateChanged, '',
td = CurrEmpID, '',
td = CurrLastName, '',
td = CurrFirstName, '',
td = CurrJobTitle, '',
td = CurrDivisionID, '',
td = CurrDepartmentID, '',
from #History
order by CurrLastName, CurrFirstName
for XML PATH('tr'), TYPE
) as NVARCHAR(MAX) )
SET @HTMLprev =
N'<H1>Staff Data changes</H1>' +
N'<table border="1">' +
N'<tr><th>Date Changed</th><th>EmpId</th>' +
N'<th>Last Name</th><th>First Name</th>' +
N'<th>Job Title</th><th>Division</th><th>Department</th>' +
CAST ( (
SELECT
td = PrevDateChanged, '',
td = PrevEmpID, '',
td = PrevLastName, '',
td = PrevFirstName, '',
td = PrevJobTitle, '',
td = PrevDivisionID, '',
td = PrevDepartmentID, ''
from #History
order by CurrLastName, CurrFirstName
for XML PATH('tr'), TYPE
) as NVARCHAR(MAX) )
set @body = @body+@HTMLcur+@HTMLprev+N'</table>' ;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply