December 23, 2014 at 9:06 am
When I added the union all portion, I am getting error 102. Without the union all, it works perfect. Do I have one character out of place?
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
USE R4W_001
SET @xml = CAST(( SELECT SALESMAN_ID AS 'td','',"td/@align"='right',
replace(convert(varchar,convert(Money, count(order_no)),1),'.00','') AS 'td','',"td/@align"='right',
replace(convert(varchar,convert(Money,sum(order_total)),1),'.00','') AS 'td','',"td/@align"='right',
replace(convert(varchar,convert(Money,((SUM(ORDER_TOTAL)) / COUNT(ORDER_NO))),1),'.00','') AS 'td'
FROM SYSOENT
WHERE (ORDER_DATE = CAST(GETDATE() AS DATE))
AND (ORDER_STATUS <> 'X') AND (ORDER_SOURCE = 'PHONE')
AND (NET_AMT > 0)
GROUP BY SALESMAN_ID
union all
(SELECT ORDER_DATE AS 'td','',''td/@align''='right',
replace(convert(varchar,convert(Money, count(order_no)),1),'.00','') AS 'td','',''td/@align''='right',
replace(convert(varchar,convert(Money,sum(order_total)),1),'.00','') AS 'td','',''td/@align''='right',
replace(convert(varchar,convert(Money,((SUM(ORDER_TOTAL)) / COUNT(ORDER_NO))),1),'.00','') AS 'td'
FROM SYSOENT
WHERE (ORDER_DATE = CAST(GETDATE() AS DATE))
AND (ORDER_STATUS <> 'X') AND (ORDER_SOURCE = 'PHONE')
AND (NET_AMT > 0)
GROUP BY ORDER_DATE
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>KPI Sales Data</H3>
<table border = 1>
<tr>
<th>SA_ID </th> <th>Total Orders</th> <th>Total Sales</th><th>AOV</th></tr>
'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Emailsvc',
@body = @body,
@body_format ='HTML',
@recipients = 'steve@xxxx.com',
@subject = 'KPI Sales Data' ;
December 23, 2014 at 9:36 am
You are missing an ending parenthesis in what you posted. I put it at the end of the CAST.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 23, 2014 at 10:02 am
I get error Incorrect syntax near '.00' and Unclosed quotation mark after the character string. I have checked all quotation marks. Any ideas?
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
USE R4W_001
SET @xml = CAST(( SELECT SALESMAN_ID AS 'td','',"td/@align"='right',
replace(convert(varchar,convert(Money, count(order_no)),1),'.00','') AS 'td','',"td/@align"='right',
replace(convert(varchar,convert(Money,sum(order_total)),1),'.00','') AS 'td','',"td/@align"='right',
replace(convert(varchar,convert(Money,((SUM(ORDER_TOTAL)) / COUNT(ORDER_NO))),1),'.00','') AS 'td'
FROM SYSOENT
WHERE (ORDER_DATE = CAST(GETDATE() AS DATE))
AND (ORDER_STATUS <> 'X') AND (ORDER_SOURCE = 'PHONE')
AND (NET_AMT > 0)
GROUP BY SALESMAN_ID
union
(SELECT ORDER_DATE AS 'td','','
replace(convert(varchar,convert(Money, count(order_no)),1),'.00','') AS 'td','',
replace(convert(varchar,convert(Money,sum(order_total)),1),'.00','') AS 'td','',
replace(convert(varchar,convert(Money,((SUM(ORDER_TOTAL)) / COUNT(ORDER_NO))),1),'.00','') AS 'td'
FROM SYSOENT
WHERE (ORDER_DATE = CAST(GETDATE() AS DATE))
AND (ORDER_STATUS <> 'X') AND (ORDER_SOURCE = 'PHONE')
AND (NET_AMT > 0)
GROUP BY ORDER_DATE
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>KPI Sales Data</H3>
<table border = 1>
<tr>
<th>SA_ID </th> <th>Total Orders</th> <th>Total Sales</th><th>AOV</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Emailsvc',
@body = @body,
@body_format ='HTML',
@recipients = 'steve@xxx.com',
@subject = 'KPI Sales Data' ;
December 24, 2014 at 6:54 am
I resolved the issue by creating a view and querying the view.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply