October 17, 2008 at 9:51 am
Hello all,
I am trying to set up an email alert which orders the table by PointDateSRC01 then takes the top 7 rows and returns them in an HTML format email. Originally started out with the first code below. This however takes the top 7 row then sorts those top 7 by PointDateSRC01. So then I tried using a sub select to order the table but you cannot use order by in a sub select so I tried using group by but that did not return the desired results. Anyone have any suggestions ? The goal is to sort the table by PointDateSRC01 then select top 7. Thank you for taking a look at this
DECLARE @tableHTML NVARCHAR(MAX)
SET @tableHTML =
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
CAST ((
SELECT TOP 7
td = HO3SRC01, '',
td = HO4SRC01, '',
td = HO6SRC01, '',
td = PointDateSRC01, '',
td = TotalCountSRC01, '',
td = TotalPremiumSRC01, ''
FROM AlertLog WITH (NOLOCK)
ORDER BY PointDateSRC01
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' ' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ct',
@recipients='ct@ics360.com',
@subject = 'Agents 1-5',
@body = @tableHTML,
@body_format = 'HTML' ;
---------------------------------------------------------------------------
END OF FIRST CODE. BELOW IS SECOND CODE
----------------------------------------------------------------------------
October 17, 2008 at 11:54 am
Put your select into a table variable - i.e.
DECLARE @tableHTML NVARCHAR(MAX)
DECLARE @CTESales TABLE
(SalesOrderID int, UnitPrice money);
WITH CTESales AS
(SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS RowID, * FROM Sales.SalesOrderDetail)
INSERT INTO@CTESales (SalesOrderID,UnitPrice)
SELECT TOP 10 SalesOrderID,UnitPrice FROM CTESales
SET @tableHTML =
N' ' +
N'
' +
N' ' +
N' ' +
CAST ( ( SELECT td = SalesOrderID,'',
td = UnitPrice,''
FROM @CTESales
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' ' ;
EXEC msdb.dbo.sp_send_dbmail
@recipients='thomas_bollhofer@symantec.com',
@subject = 'TEST',
@body = @tableHTML,
@body_format = 'HTML' ;
Tommy
Follow @sqlscribeOctober 17, 2008 at 11:55 am
Just a note that this WYSWYG editor is stripping out some of the HTML tags 🙂
Tommy
Follow @sqlscribeOctober 17, 2008 at 3:58 pm
WOW im such a doof all i had to do was put the ORDER BY outside of the select like this. thanks for the advice though
DECLARE @tableHTML NVARCHAR(MAX)
SET @tableHTML =
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
CAST ((
SELECT TOP 7
td = HO3SRC01, '',
td = HO4SRC01, '',
td = HO6SRC01, '',
td = PointDateSRC01, '',
td = TotalCountSRC01, '',
td = TotalPremiumSRC01, ''
FROM
(
SELECT
HO3SRC01,
HO4SRC01,
HO6SRC01,
PointDateSRC01,
TotalCountSRC01,
TotalPremiumSRC01
FROM AlertLog WITH (NOLOCK)
) AS CompleteDataSet ORDER BY PointDateSRC01 DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
N' ' ;
-Chris
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply