Sql-server – MAX Query returns set results when using dbmail

sql servert-sqlxml

I am setting up alerts in a system and I want to query a db table and get the max date passed in to a data row and only that one entry.

For some reason, when I do it, the HTML email that comes through has the same date over and over again.

How do I get it to where it stops repeating itself and just does the max date only?

SET @tableHTML16 =
    N'<H1>BDI Feeds Last Received Batch</H1>' +
    N'<table border="1">' +
    N'<tr><th>Cerner</th><th>CPACS</th><th>DSCH123</th>' +
    CAST  ((SELECT   td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_Cerner_BDI:OCBDI1'), '',
                     td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_CPACS_BDI:OCBDI1'), '',
                     td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_DSCH123_BDI:OCBDI1'), ''

            from eiwdata..eiwtcontentreceived (nolock)


        FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

You can see what it looks like when it gets to this portion of the script by looking in sysmail all items:

<H1>BDI Feeds Last Received Batch</H1>
<table border="1">
<tr><th>Cerner</th><th>CPACS</th><th>DSCH123</th>
<tr><td>2018-02-28T17:28:13.450</td><td>2018-02-28T16:31:51.113</td><td>2018-02-28T16:55:35.433</td></tr>
<tr><td>2018-02-28T17:28:13.450</td><td>2018-02-28T16:31:51.113</td><td>2018-02-28T16:55:35.433</td></tr>
<tr><td>2018-02-28T17:28:13.450</td><td>2018-02-28T16:31:51.113</td><td>2018-02-28T16:55:35.433</td></tr>
<tr><td>2018-02-28T17:28:13.450</td><td>2018-02-28

I of course had to shorten it, but it just goes over and over again.

Best Answer

You have at least one from eiwdata..eiwtcontentreceived (nolock) too many. You are fetching the max value for each row in eiwdata..eiwtcontentreceived. 

SET @tableHTML16 =
    N'<H1>BDI Feeds Last Received Batch</H1>' +
    N'<table border="1">' +                          -- close </tr> here
    N'<tr><th>Cerner</th><th>CPACS</th><th>DSCH123</th></tr>' +
    CAST  ((SELECT   td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_Cerner_BDI:OCBDI1'), '',
                     td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_CPACS_BDI:OCBDI1'), '',
                     td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_DSCH123_BDI:OCBDI1'), ''

/*
             This table reference should be removed
             from eiwdata..eiwtcontentreceived (nolock)
*/    

        FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
Related Question