Total of Column in sql email - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Total of Column in sql email

Author

Topic

Mallen
Starting Member

27 Posts

Posted - 2010-10-27 : 14:13:35

So I finally got a table to send in an email directly from a procedure and now they want a sum total on the bottom of the table and I'm stuck.
I need advice on how to either 1) pass more than one query into the email so I can get the sum in sql or 2) find some method of html/php/something that I can pass into the table to create a sum row.

Here is the total procedure:
-----------------------------
CREATE PROCEDURE [dbo].[Five9Email]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @nonblank TABLE
(
Campaign varchar(25),
[count] int
)
INSERT INTO @nonblank
SELECT
Campaign,
count(*)
FROM Five9CSV
WHERE ISNUMERIC(Five9CSV.LRCS)=1
GROUP BY Campaign

-- Email report

DECLARE @tableHTML NVARCHAR(MAX);

SET @tableHTML =
N'<H1>TS Results Report</H1>' +
N'<table border="1">' +
N'<tr>Campaign</th><th>Count</th><th>Non Blank Count</th>' +
CAST(( SELECT
td = f.Campaign, '',
td= count(*), '',
td= n.[count]
FROM Five9CSV f INNER JOIN @nonblank n ON f.Campaign = n.Campaign
GROUP BY f.Campaign, n.[count]
FOR XML PATH('tr'), TYPE)
AS NVARCHAR(MAX)) +
N'</table>';

EXEC msdb.dbo.sp_send_dbmail @recipients='procedures@xxx.com',
@subject = 'TS Results',
@body = @tableHTML,
@body_format = 'HTML';

END

--------------
Here is a sample of the current results table (except in pretty formatted html table form which I can't display here):
--------------
TS Results Report
Campaign Count Non Blank Count
[None] 88 70
ACA_1 312 311
AGF_1 2500 2500
Broken_Promise 1736 1734
CSI_1 1922 1922
Inbound Campaign 436 286
Messaging 9319 9319
Misc. 2352 2352
Outbound 163 132
SR_1 2235 2233

So I need some way to throw a sum at the bottom of the Count and Non Blank Count columns.
Any advise and help very much appreciated. I'm stuck

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-27 : 14:17:20

Add WITH CUBE after GROUP BY in SELECT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mallen
Starting Member

27 Posts

Posted - 2010-10-27 : 14:46:37

That does help a little except it really destroys the table. Adding WITH CUBE gives me the following table output:

TS Results Report
Campaign Count Non Blank Count
[None] 88 70
[None] 88
ACA_1 312 311
ACA_1 312
AGF_1 2500 2500
AGF_1 2500
Broken_Promise 1736 1734
Broken_Promise 1736
CSI_1 1922 1922
CSI_1 1922
Inbound Campaign 436 286
Inbound Campaign 436
Messaging 9319 9319
Messaging 9319
Misc. 2352 2352
Misc. 2352
Outbound 163 132
Outbound 163
SR_1 2235 2233
SR_1 2235
21063
88 70
163 132
436 286
312 311
1736 1734
1922 1922
2235 2233
2352 2352
2500 2500
9319 9319

It basically just repeats all the totals I already have, then gives me the total of 21063 that I need reported and then gives me a bunch of useless data. :( So is there a way to clean all the other crap out and just keep the 21063 count total I need and also add a total for the non-blank column?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-27 : 14:53:33

put it in temporary table and while retrieving from temporary table add a filter condition like (Campaign is null and Count is null) or (Campaign is not null and Count is not null)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mallen
Starting Member

27 Posts

Posted - 2010-10-27 : 15:30:20

I can use WITH ROLLUP and cut down all the number trash at the end of the table but I can't find a WHERE condition that will give me just the total I need.
If I use (campaign is null and non-blank is null) I can get the total of the count column but no other records.
If I use (campaign is null and count is null) it returns nothing.
If I use (campaign is not null and count is not null) it returns all the records except for the count total.

That leaves me back to square one with no way to have a table with all the records and two column totals. It also doesn't provide the total of the non-blank column at all.

So frustrating.

Mallen
Starting Member

27 Posts

Posted - 2010-10-27 : 16:27:10

Ok I got it to work. The trick is the use of an html footer.
Here is the correctly working code and table for future refrence:
DECLARE @nonblank TABLE
(
Campaign varchar(25),
[count] int
)
INSERT INTO @nonblank
SELECT
Campaign,
count(*)
FROM Five9CSV
WHERE ISNUMERIC(Five9CSV.LRCS)=1
GROUP BY Campaign

DECLARE @nonblanksum TABLE
(
[count] int
)
INSERT INTO @nonblanksum
SELECT
count(*)
FROM Five9CSV
WHERE ISNUMERIC(Five9CSV.LRCS)=1

-- Email report

DECLARE @tableHTML NVARCHAR(MAX);

SET @tableHTML =
N'<H1>TS Results Report</H1>' +
N'<table border="1">' +
N'<tr>Campaign</th><th>Count</th><th>Non Blank Count</th>' +
CAST(( SELECT
td = f.Campaign, '',
td= count(*), '',
td= n.[count]
FROM Five9CSV f INNER JOIN @nonblank n ON f.Campaign = n.Campaign
GROUP BY f.Campaign, n.[count]
FOR XML PATH('tr'), TYPE)
AS NVARCHAR(MAX)) +
N'<tfoot>' +
N'<tr><td>Total Count</td><td>Total Non-Blank Count</td>' +
CAST(( SELECT
td= count(*), '',
td= n.[count]
FROM Five9CSV f, @nonblanksum n
GROUP BY n.[count]
FOR XML PATH('tr'), TYPE)
AS NVARCHAR(MAX))+
N'</tfoot>' +
N'</table>';

EXEC msdb.dbo.sp_send_dbmail @recipients='mallen@xxx.com',
@subject = 'TS Results',
@body = @tableHTML,
@body_format = 'HTML';
---------------------
TS Results Report
Campaign Count Non Blank Count
[None] 88 70
Outbound 163 132
Inbound Campaign 436 286
ACA_1 312 311
Broken_Promise 1736 1734
CSI_1 1922 1922
SR_1 2235 2233
Misc. 2352 2352
AGF_1 2500 2500
Messaging 9319 9319
Total Count Total Non-Blank Count
21063 20859

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources