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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy