September 16, 2014 at 8:11 am
Hello all,
Below is a section of a peice of code that I have been handed to run that sends out daily telephone stats.
Is there anyway I can get a Sub-Totals row for each Department, and a Overall Total row included?
SET @MailSubject = 'Phone Stats Summary (Ops) - ' + @MailSubject + ' Abandoned'
SET @THeader =
(SELECTTH = 'Department', ''
, = '12.5%'
, TH = 'Lines', ''
, = '12.5%'
, TH = 'Offered', ''
, = '12.5%'
, TH = 'Answered', ''
, = '12.5%'
, TH = 'PCA', ''
, = '12.5%'
, TH = '% Abandoned', ''
, = '12.5%'
, TH = 'AWT', ''
, = '12.5%'
, TH = 'Ans In 60 Secs', ''
FOR XML PATH('THEAD'), TYPE
)
SET @Message = '<H5>Summary for Yesterday (' + CONVERT(VARCHAR(11), @ReportDate, 113)+ ')</H5>'
SET @Message = @Message + CAST((SELECT @THeader
, (SELECT eRpt.udf_PhoneStatsTBODY('YDay'))
FOR XML PATH ('TABLE'))
AS VARCHAR(MAX))
+ '<H5>Current Work Summary (' + CONVERT(VARCHAR(5), @MaxTicketTime, 108) + ')</H5>'
+ ISNULL(@WorkSummary, '')
+ '
<H5>Call Arrival Patterns (' + CONVERT(VARCHAR(11), @ReportDate, 113)+ ')</H5>'
+ CAST(eRpt.udf_PhoneStatsTINTERVAL() AS VARCHAR(MAX))
END
ELSE
BEGIN
SET @MailSubject = 'Phone Stats Summary (Ops) - Weekly Summary'
SET @Message = '<H5>Summary for The Past Week</H5>'
SET @Message = REPLACE(CAST((SELECT eRpt.udf_PhoneStatsWeekTBODY()) AS VARCHAR(MAX)), '</BR>', '')
+ '<H5>Current Work Summary (' + CONVERT(VARCHAR(5), @MaxTicketTime, 108) + ')</H5>'
+ ISNULL(@WorkSummary, '')
+ CHAR(10) + '<P>Here is a summary of the current SLA Performance:</P>'
+ CHAR(10) + eRpt.udf_SLA_SummaryTable()
+ CHAR(10) + '<P>Below is a breakdown of each SLA:</P>'
+ CHAR(10) + CAST((SELECT eRpt.udf_SLA_DetailTable()) AS VARCHAR(MAX))
END
September 16, 2014 at 8:20 am
normally, you would do this WITH ROLLUP in T-SQL.
http://technet.microsoft.com/en-us/library/ms189305(v=sql.90).aspx
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply