July 21, 2011 at 2:43 pm
I'm trying to send an HTML table using sp_send_dbmail. I want to add another column to the table that has different WHERE parameters than the first column though. This is what I have so far, and it says that I have incorrect syntax near the keyword 'FOR'.
N'<H4>Last Two Days</H4>' +
N'<table border = "1">' +
N'<tr><th>Group</th>' +
N'<th>Wednesday</th>' +
N'<th>Tuesday</th>' +
CAST ( (
SELECT *
FROM(SELECT
td = g.GroupName, '',
td = CAST(SUM( ISNULL(CASE WHEN c.Contact = 1 THEN 1 ELSE 0 END, 0) ) / (((sum(isnull(talktime,0)) + sum(isnull(wraptime,0)) + sum(isnull(previewtime,0)) + sum(isnull(waittime,0)) + sum(isnull(deadtime,0))) / 60) / 60) as decimal(10,2)), ''
FROM History h
LEFT JOIN Project p ON p.ProjectID = h.ProjectID
LEFT JOIN CRC c on c.CRC = h.CRC
JOIN ProjectGroups g on H.ProjectID = g.ProjectID
WHERE CONVERT(varchar(30), (DATEADD(DAY, DATEDIFF(day, 0, CallDateTime), -0)), 101)
= CONVERT(varchar(30), (DATEADD(DAY, DATEDIFF(day, 0, (GETDATE() -1)), 0)), 101)
AND
(g.GroupName = 'EDU' OR g.GroupName = 'ADX'
OR g.GroupName = 'ADT' OR g.GroupName = 'SEMREG'
OR g.GroupName = 'AMSEM' OR g.GroupName = 'BRMQ')
GROUP BY G.GroupName
UNION
SELECT
td = CAST(SUM( ISNULL(CASE WHEN c.Contact = 1 THEN 1 ELSE 0 END, 0) ) / (((sum(isnull(talktime,0)) + sum(isnull(wraptime,0)) + sum(isnull(previewtime,0)) + sum(isnull(waittime,0)) + sum(isnull(deadtime,0))) / 60) / 60) as decimal(10,2)), ''
FROM History h
LEFT JOIN Project p ON p.ProjectID = h.ProjectID
LEFT JOIN CRC c on c.CRC = h.CRC
JOIN ProjectGroups g on H.ProjectID = g.ProjectID
WHERE CONVERT(varchar(30), (DATEADD(DAY, DATEDIFF(day, 0, CallDateTime), -0)), 101)
= CONVERT(varchar(30), (DATEADD(DAY, DATEDIFF(day, 0, (GETDATE() -2)), 0)), 101)
AND
(g.GroupName = 'EDU' OR g.GroupName = 'ADX'
OR g.GroupName = 'ADT' OR g.GroupName = 'SEMREG'
OR g.GroupName = 'AMSEM' OR g.GroupName = 'BRMQ')
GROUP BY G.GroupName)
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) )
+ N'</table>'
Your help would be much appreciated.
July 21, 2011 at 2:44 pm
Could you include the exact error message?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 21, 2011 at 2:45 pm
Msg 156, Level 15, State 1, Line 50
Incorrect syntax near the keyword 'FOR'.
July 21, 2011 at 2:47 pm
Does the inner query, with the Union clause and all that, work without the For XML clause?
If so, does it work with the For XML clause, without the outer part of the query?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 21, 2011 at 3:01 pm
Without all the HTML stuff, yes, it does run and return results.
And if I dont add the second column for the GETDATE()-2, it runs just fine. It just doesnt like that I am trying to add another column with different parameters for some reason. Is there another way to go about this?
July 21, 2011 at 3:34 pm
I think you need to remove ")" from your code at 4th line from the bottom. It should be
GROUP BY G.GroupName
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) )
+ N'</table>'
--------------------------------------------------------------
DBA or SQL Programmer? Who Knows. :unsure:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply