create total record in html email
-
I have a table with 3 columns Entity,Description, and a TotAmt. I want to create html email with entity column across the top
and Description be my rows with associated totamt. I want totals for each div column.
Data:
240,Frt,2200
240,acc,220
250,Acc,300
250,,Frt,300
Report Example:
240 250
Frt 2200 300
Acc 220 300
Total 2400 600
Thanks.
-
Please provide a sample set of data and expected results. The 'data' you provided doesn't tell us what the div column is - or how to interpret what data needs to be cross-tabbed (pivoted).
And since you want this in an email - what is the format of the email? Is that an embedded table, attached file - something else?
I believe I have already provided templates for sending email from SQL Server using sp_send_dbmail. Is there something with those examples that you need help with?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
The email will be an embedded table..
Insert Into frt_tracker
Values('240','Frt','2200')
Insert Into frt_tracker
Values('240','Acc','200')
Insert Into frt_tracker
Values('440','Brd','20')
Report Example:
240 440
Frt 2200
Acc 220
Brd 20
Total 2400 20
I would like a total line for each Entity.
CREATE TABLE [dbo].[frt_tracker](
[Entity] int Not NULL,
[Description] [nvarchar](50) NULL,
[TotAmt] [decimal](18, 0) NULL
) ON [PRIMARY] -
I've been trying to find the thread that has your examples..
-
https://www.sqlservercentral.com/forums/topic/create-html-email#post-3889947
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
To get the results - you need to use GROUP BY with GROUPING SETS and GROUPING_ID. There are plenty of examples in BOL to show how that can be done. If the real issue is that you don't know how many Entities will be included - then you will need a dynamic cross-tab/pivot solution.
Here is an article from this site: https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
Once you have reviewed BOL and the above article - if you still need help with something specific in implementing the final solution then post what you have done so far and where you are having issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I do the preagg then the crosstab and finally dynamic column for Entity, but when I do the Execute(SQL) it throws an
error that I can't seem to figure out. I want a Total for each Description associated with an Entity then a Grand Total
for each entity.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '='.
Msg 319, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
--===== Define the "main" SQL and the variable to accumulate column names in.
DECLARE @SQL NVARCHAR(MAX) = N'
WITH ctePreAgg AS
(
SELECT entity,description, totalamt = SUM(totalamt)
FROM freight_Tracker
GROUP BY entity,description
)
Insert Into #Aggs
SELECT entity = IIF(GROUPING(entity)=1, ''*** GRAND ***'',entity)
,description = IIF(GROUPING(description)=1 , ''*** TOTAL ***'',description)<<@Columns>>
,TotalAMt = SUM(TotalAmt)
FROM ctePreAgg
GROUP BY entity,description WITH ROLLUP
ORDER BY GROUPING(entity),entity,GROUPING(description),description;'
,@Columns NVARCHAR(MAX) = ''
;
--===== Dynamically create the Category column dynamic SQL from the table data.
SELECT @Columns += NCHAR(10)+SPACE(8)+N','
+ entity+' = SUM(IIF(entity = N'''+entity+''',totalamt,0))'
FROM freight_Tracker
GROUP BY entity
ORDER BY entity ;
--===== Add the Category columns to the main dynamic SQL
SELECT @SQL = REPLACE(@SQL,N'<<@Columns>>',@Columns)
;
--===== Print, then execute the dynamic SQL.
PRINT @SQL; -
Now that I think about this - you don't actually need a dynamic cross-tab. Because you want this output to an html email - the FOR XML code will dynamically cross-tab the data for you.
Here is a thread where this is shown: https://www.sqlservercentral.com/forums/topic/dynamic-parameter-transfer-in-msdb-dbo-sp_send_dbmail
The key here is to create the header and the detail using the same approach - except for the header you return the column name and for the detail you return the column value.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Will I still be able to create the totals\Grand at the Description and Entity levels.
-
Yes - the totals are calculated using grouping statements and the pivot is done using FOR XML. Or - you can pivot the data into a temp table and dynamically build the query.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I think that's what I tried in an earlier post but had an error can you take a look ..
Thanks.
-
I looked at your earlier attempt - which doesn't look anything like the cross-tab examples. Since you have not provided data in a consumable format (create/insert statements) it is really hard to provide a working solution. The fact that your original request didn't mention the fact that you actually want a dynamic cross-tab doesn't help.
Please provide a larger sample set of data - with expected results and when I have some time I will see if I can put together a working solution.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thanks, the output will have Entity going across the top of the email, and rows will be the Description, and it's associated totalamt. Then at the bottom of the email will be a Total line for each of the Descriptions by Entity. Not sure if a Grand Total could be included or not, but would be great.
Report Example:
Entity Entity Entity
Description xxx xx xx
Total xxxx xxx xxx
CREATE TABLE [dbo].[frt_tracker](
[Entity] int Not NULL,
[Description] [nvarchar](50) NULL,
[TotAmt] [decimal](18, 0) NULL
) ON [PRIMARY]
Insert Into frt_tracker
Values('200','Rebate','400')
Insert Into frt_tracker
Values('300','Rebate','600')
Insert Into frt_tracker
Values('600','Rebate','2000')
Insert Into frt_tracker
Values('200','Payment','200')
Insert Into frt_tracker
Values('300','Payment','230')
Insert Into frt_tracker
Values('600','Payment','4500')
Insert Into frt_tracker
Values('200','Expense','200') -
This should get you close - you still need to build the rest of the HTML code but that all exists in the examples I linked to.
Drop Table If Exists #frt_tracker;
Create Table #frt_tracker (Entity int, Description nvarchar(50), TotAmt decimal(18,0));
Insert Into #frt_tracker(Entity, [Description], TotAmt)
Values (200, 'Rebate', 400)
, (300, 'Rebate', 600)
, (600, 'Rebate', 2000)
, (200, 'Payment', 200)
, (300, 'Payment', 230)
, (600, 'Payment', 4500)
, (200, 'Expense', 200);
Declare @body nvarchar(max)
, @xmlResults varchar(max)
, @tableHeader varchar(max)
, @recipients varchar(max) = 'your.email@domain.com'
, @cc_recipients varchar(max) = '';
--==== Create the table header
Select @tableHeader = concat('<tr>', cast(stuff((Select h.Entity As th, ''
From (Select Distinct
ft.Entity
From #frt_tracker ft
) As h
Order By
h.Entity
For Xml Path('th')), 1, 0, '<th></th>') As varchar(max)), '</tr>)');
Select @tableHeader;
--==== Build the table data and return in the xmlResults variable
Declare @columnList varchar(max) = cast((Select concat(', sum(Case When ft.Entity = ', h.Entity, ' Then ft.TotAmt End) As td, ''''')
From (Select Distinct
ft.Entity
From #frt_tracker ft
) As h
Order By
h.Entity
For Xml Path('')) As varchar(max))
--==== Create the cross-tab query and format as XML for table details
Declare @sqlCommand nvarchar(max) = '
Select @xmlResults = cast((Select Case When grouping_id(ft.[Description]) = 1 Then ''Totals'' Else ft.[Description] End As td, ''''
' + @columnList + '
From #frt_tracker ft
Group By Grouping Sets (
()
, (ft.[Description])
)
Order By grouping_id(ft.[Description])
For Xml Path(''tr'')) As varchar(max))';
Print @sqlCommand;
Execute sp_ExecuteSql @sqlCommand, N'@xmlResults varchar(max) out', @xmlResults = @xmlResults out;
Select cast(@xmlResults As xml);This does use dynamic SQL - which wouldn't be necessary if you had a fixed number of entities. One feature of using PIVOT and FOR XML is that any columns that do not exist in the source don't end up in the results. So - if you only have entities 200, 300 and 600 and the results only include data for entity 200 then the table generated would only include entity 200.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
First, here's a shorter way to provide the data in a readily consumable format. It's also better to do it in a temp table than a real table in most cases because people don't want your junk mixing with their junk. 😀
DROP TABLE IF EXISTS #frt_tracker;
CREATE TABLE #frt_tracker
(
Entity INT NOT NULL
,Description NVARCHAR(50) NULL
,TotAmt DECIMAL(18,0) NULL
)
;
INSERT INTO #frt_tracker WITH (TABLOCK)
(Entity,Description,TotAmt)
VALUES (200,'Rebate' , 400)
,(300,'Rebate' , 600)
,(600,'Rebate' ,2000)
,(200,'Payment', 200)
,(300,'Payment', 230)
,(600,'Payment',4500)
,(200,'Expense', 200)
;Now, for that data given, here's the "hard-coded" solution. This is what is known as a "CROSSTAB" and you can read about it at the following URL. And, yes, you should read about it because it's one of the fundamental principles of all reporting.
SELECT Entity = IIF(GROUPING(Entity) = 0,CONVERT(NVARCHAR(5),Entity),N'Total')
,Expense = SUM(IIF(Description = N'Expense',TotAmt,0))
,Payment = SUM(IIF(Description = N'Payment',TotAmt,0))
,Rebate = SUM(IIF(Description = N'Rebate' ,TotAmt,0))
FROM #frt_tracker
GROUP BY Entity WITH ROLLUP
ORDER BY GROUPING(Entity),Entity
;That returns the following output and you should have included it in your response because it usually answers a thousand unanswered questions. The stuff you posted was just plain lazy and people aren't going to want to help you.
I also strongly recommend you read up on the ROLLUP (and the related CUBE) as well as the power contained in the GROUPING() function to do some formatting magic.
The GROUPING SETS thing is nice but just not necessary about 90% of the time. It IS a skill that's needed though because it makes that other 10% a whole lot easier.
The real key is that you MUST first make the hard-coded version work so you don't have to try to write the original problem as dynamic SQL because everyone sucks at doing that. Once you have it working, then just like it does in the article that Jeffrey previously pointed you to and using another "Black Arts" method of token replacement, the rest is damned near a cake walk. All you have to do is assign the first static part of the SQL to the variable, use the magic of a "Pseudo-Cursor" to build the dynamic part from the table as the second part, and then assign the static remainder as the third part and Bob's your uncle. I've added comments to the code so you can see what's going on. The code itself is very short.
Here's the link that Jeffrey previously pointed to... heh... yeah... I'm good friends with the author of both of those articles. 😀
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
--===== Declare the @SQL Variable and assign the static part of the SELECT to it.
DECLARE @SQL NVARCHAR(4000) = N'
SELECT Entity = IIF(GROUPING(Entity) = 0,CONVERT(NVARCHAR(5),Entity),N''Total'')'
;
--===== Add the dynamic part of the SELECT list to the dynamic SQL.
-- Notice that this is the same for all the lines. Only the description changes.
-- We use a "template" of the line and use the natural "Pseudo-Cursor" action of
-- the SELECT/GROUP BY to get an ordered list of the Descriptions to use for the
-- REPLACEs. One of the REPLACES replaces the Description and the other replaces
-- the double quotes with 2 single quotes. I put Description in braces just in
-- case there are spaces or other odd characters in the Description.
SELECT @SQL += REPLACE(REPLACE(N'
,[<<Description>>] = SUM(IIF(Description = N"<<Description>>",TotAmt,0))'
--Other end of the REPLACEss
,N'"',N'''')
,N'<<Description>>',Description)
FROM #frt_tracker
GROUP BY Description
ORDER BY Description
;
--===== Add the rest of the fixed SQL to the dynamic SQL.
SELECT @SQL += '
FROM #frt_tracker
GROUP BY Entity WITH ROLLUP
ORDER BY GROUPING(Entity),Entity
;'
;
--===== Display the dynamic sql if you want to check it.
PRINT @SQL
;
--===== Execute the dynamic SQL.
EXEC (@SQL)
;Now duck because there will be some people coming that will absolutely rail against the variable overlay method I used. They're quite right that it frequently fails but, once you know what makes it fail, you can avoid that like we have in this particular bit of code. If you want to use the FOR XML PATH method as a guarantee against failing, don't forget to use "TYPE" to ensure the de-entitization of any special characters and make sure that you have the proper sub-query join if there are multiple elements and... and... etc, etc.
The code produced by the dynamic SQL above looks like this... yeah... I take the time to format even my dynamic SQL so that it's readable and it's just not difficult to do if your original working code is formatted.
SELECT Entity = IIF(GROUPING(Entity) = 0,CONVERT(NVARCHAR(5),Entity),N'Total')
,[Expense] = SUM(IIF(Description = N'Expense',TotAmt,0))
,[Payment] = SUM(IIF(Description = N'Payment',TotAmt,0))
,[Rebate] = SUM(IIF(Description = N'Rebate',TotAmt,0))
FROM #frt_tracker
GROUP BY Entity WITH ROLLUP
ORDER BY GROUPING(Entity),Entity
;Again... I strongly recommend that you get really good at this method of "Converting Rows to Columns" because it's one of the true fundamentals of knowing how to code in T-SQL especially when you have to generate your output for spreadsheet users, etc. And if you don't think that spreadsheets don't make the world go'round, you need to spend more time in the field. 😀 Heh... you should never need to ask questions about how to do this type of thing ever again, right? 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply