CREATE TABLE #EqualData
(
Centerid [int],
Centername [VARCHAR] (50),
Warehouse FLOAT,
BulkLoad FLOAT,
Difference FLOAT,
Transactiondate datetime
)
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
Select Centerid,CAst(Warehouse as varchar) as Warehouse ,Centername,
cast(BulkLoad as varchar) as BulkLoad,Replace(Cast(Difference as Varchar),'-','') as Difference ,
Transactiondate FROM #EqualData
SET @xml = CAST(( SELECT [Centerid] AS 'td','',[Centername] AS 'td','',
[Warehouse] AS 'td','', BulkLoad AS 'td','', Difference as 'td','', Transactiondate as 'td'
FROM #EqualData ORDER BY Centerid
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>BulkLoad Difference</H3>
<table border = 1>
<tr>
<th> Centerid </th> <th> Centername </th> <th> Warehouse </th> <th> BulkLoad </th> <th> Difference </th> <th> Transactiondate </th> </tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'test',
@recipients='V.Vinoth@medallit.in' ,
@subject ='Amount Difference in BulkLoad' ,
@body = @body,
@body_format ='HTML' ;
No comments:
Post a Comment