CODEWITHSUNDEEP
sqlsql-server
Prohiller
Prohiller

Reputation: 3

Send subquery result by html formated email

I have working query:

SELECT TOP (1000) [dashboard_name]
,[dashboard_path]
,[UserName]
,[timestamp]
,(SELECT TOP(1) DATEDIFF(day, timestamp, GETDATE()) FROM [Monitoring].[dbo].[Temporary_logins_log] t WHERE t.dashboard_name = dashboard_name AND t.dashboard_path = dashboard_path AND t.UserName = UserName) AS [Age] 
FROM [Monitoring].[dbo].[Temporary_logins_log] where timestamp = CONVERT(VARCHAR(10), getdate(), 111)

And I would like to send results by mail using:

DECLARE @tableHTML  NVARCHAR(MAX) ;  

SET @tableHTML =  
    N'<H1>Header</H1>' +  
    N'<table border="1">' +  
    N'<tr><th>Report Name</th><th>Report Path</th>' +  
    N'<th>LoginName</th><th>Date</th><th>Age (days)</th></tr>' +  
    CAST ( ( SELECT td = t.dashboard_name, '',  
                    td = t.dashboard_path, '',  
                    td = t.UserName, '',  
                    td = t.timestamp, '',
                    td = (SELECT TOP(1) DATEDIFF(day, l.timestamp, GETDATE()) FROM [Monitoring].[dbo].[Temporary_logins_log] l WHERE t.dashboard_name = l.dashboard_name AND t.dashboard_path = l.dashboard_path AND t.UserName = l.UserName) AS [Age], ''
              FROM [Monitoring].[dbo].[Temporary_logins_log] t where timestamp = CONVERT(VARCHAR(10), getdate(), 111)
              FOR XML PATH('tr'), TYPE   
    ) AS NVARCHAR(MAX) ) + N'</table>' ;  

EXEC msdb.dbo.sp_send_dbmail @recipients='[email protected]',  
    @subject = 'SQL Report',  
    @body = @tableHTML,  
    @body_format = 'HTML' ;

but I'm getting syntax error near the keyword AS. Is it possible to send subquery result using this code or do I have to rework it into joining the table with itself, without using the subquery?

Upvotes: 0

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

This line in the query doesn't make sense:

td = (SELECT TOP(1) DATEDIFF(day, l.timestamp, GETDATE()) 
      FROM [Monitoring].[dbo].[Temporary_logins_log] l 
      WHERE t.dashboard_name = l.dashboard_name AND 
            t.dashboard_path = l.dashboard_path AND
             t.UserName = l.UserName
     ) AS [Age], ''

Do you want the column alias to be td or [Age]? I am guessing td. If so, remove the as [Age].

Upvotes: 1

Related Questions