July 17, 2017 at 5:12 am
Hi guys, I have created a HTML table dynamically to send on mail. That table contains few amount columns. It's something like:
Declare @htmltable varchar(max)
Select @htmltable = '<table border="1" style="fine-tune:verdana;font size=6">
<th>Business Date</th>
<th>Trading Type</th>
<th>Trade Amt</th>
<th>Revenue Amt</th></tr>
Select @htmltable= @htmltable + cast(
(Select convert(varchar(12), cobdate, 107) as 'BusinessDate/td',
TradingType as 'TradingType/td',
Cast(TradeAmt as varchar(100)) as 'TradeAmt/td',
Cast(RevenueAmt as varchar(100)) as 'RevenueAmt/td'
From dbo.RevGenerateByTrade
Where IsActive =1
for XML PATH('tr')
)
As nvarchar(max))
I add it to body part of html and the above code works fine. However is there a way to:
1. Show the amount columns as , separated. Eg 12,345 instead of 12345.
2. Is there a way to show the negative amt as red color font even if there is a column like "Target Amt Range" for which values will be 10000/(-10000).
July 17, 2017 at 6:10 am
This should do the job:CREATE TABLE #Sample
(cobdate date,
TradingType varchar(10),
Tradeamt decimal(12,2),
RevenueAmt decimal(12,2),
IsActive bit);
GO
INSERT INTO #Sample
VALUES
(GETDATE(), 'ABC',123456,2345,1),
(GETDATE(), 'DEF',123,1,1),
(GETDATE(), 'GHI',-1700,-400,1),
(GETDATE(), 'DEF',0,0,1);
GO
DECLARE @HTMLTable varchar(max);
SET @HTMLTable =
'<table border="1" style="fine-tune:verdana;font size=6">
<th>Business Date</th>
<th>Trading Type</th>
<th>Trade Amt</th>
<th>Revenue Amt</th></tr>';
SET @HTMLTable = @HTMLTable + CAST((
SELECT CONVERT(varchar(12), cobdate, 107) AS [BusinessDate/td],
TradingType AS [TradingType/td],
--The below actually uses the format #,0.00, but if you need to change this you could use STUFF to remvoe the last 3 characters.
--The case statement adds a font Markup around the value, if it is less than zero.
CASE WHEN TradeAmt < 0 THEN '<font color="red">' ELSE '' END + CONVERT(varchar(100),CONVERT(money,TradeAmt),1) + CASE WHEN TradeAmt < 0 THEN '</font>' ELSE '' END AS [TradeAmt/td] ,
CASE WHEN RevenueAmt < 0 THEN '<font color="red">' ELSE '' END + CONVERT(varchar(100),CONVERT(money,RevenueAmt),1) + CASE WHEN RevenueAmt < 0 THEN '</font>' ELSE '' END AS [RevenueAmt/td]
FROM #Sample
WHERE IsActive =1
FOR XML PATH('tr')) AS varchar(MAX));
SET @HTMLTable = REPLACE(REPLACE(@HTMLTable, '>', '>'), '<', '<'); --Remove Escape Characters
--Use sp_send_dbmail on msdb.
EXEC msdb.dbo.sp_send_dbmail @profile_name = ..., @body = @HTMLTable, ...;
GO
DROP TABLE #Sample;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 17, 2017 at 6:27 am
For the RevenueAmt, if the datatype is MONEY, the convert function has an additional parameter and will insert your commas for you. if it si a decimal, you have to convert to moeny before you convert to charDECLARE @RevenueAmt Decimal(19,4)=12345
DECLARE @MoneyAmt money=12345
select CONVERT(varchar,@MoneyAmt,1),
CONVERT(varchar,@RevenueAmt,1),
CONVERT(varchar,CONVERT(money,@RevenueAmt),1)
for turning items red, you ant to use css and a class to select the class dynamically, but I haven't been able to get that to generate from a case statement yet.
Lowell
July 18, 2017 at 5:05 am
Lowell - Monday, July 17, 2017 6:27 AMFor the RevenueAmt, if the datatype is MONEY, the convert function has an additional parameter and will insert your commas for you. if it si a decimal, you have to convert to moeny before you convert to charDECLARE @RevenueAmt Decimal(19,4)=12345
DECLARE @MoneyAmt money=12345
select CONVERT(varchar,@MoneyAmt,1),
CONVERT(varchar,@RevenueAmt,1),
CONVERT(varchar,CONVERT(money,@RevenueAmt),1)for turning items red, you ant to use css and a class to select the class dynamically, but I haven't been able to get that to generate from a case statement yet.
Lowell, thanks but after converting it to money it contains those values post decimal. How can that be removed ? I want it in format 12,345 and not 12,345.00
July 18, 2017 at 5:09 am
Thom A - Monday, July 17, 2017 6:10 AMThis should do the job:CREATE TABLE #Sample
(cobdate date,
TradingType varchar(10),
Tradeamt decimal(12,2),
RevenueAmt decimal(12,2),
IsActive bit);
GOINSERT INTO #Sample
VALUES
(GETDATE(), 'ABC',123456,2345,1),
(GETDATE(), 'DEF',123,1,1),
(GETDATE(), 'GHI',-1700,-400,1),
(GETDATE(), 'DEF',0,0,1);
GODECLARE @HTMLTable varchar(max);
SET @HTMLTable =
'<table border="1" style="fine-tune:verdana;font size=6">
<th>Business Date</th>
<th>Trading Type</th>
<th>Trade Amt</th>
<th>Revenue Amt</th></tr>';SET @HTMLTable = @HTMLTable + CAST((
SELECT CONVERT(varchar(12), cobdate, 107) AS [BusinessDate/td],
TradingType AS [TradingType/td],
--The below actually uses the format #,0.00, but if you need to change this you could use STUFF to remvoe the last 3 characters.
--The case statement adds a font Markup around the value, if it is less than zero.
CASE WHEN TradeAmt < 0 THEN '<font color="red">' ELSE '' END + CONVERT(varchar(100),CONVERT(money,TradeAmt),1) + CASE WHEN TradeAmt < 0 THEN '</font>' ELSE '' END AS [TradeAmt/td] ,
CASE WHEN RevenueAmt < 0 THEN '<font color="red">' ELSE '' END + CONVERT(varchar(100),CONVERT(money,RevenueAmt),1) + CASE WHEN RevenueAmt < 0 THEN '</font>' ELSE '' END AS [RevenueAmt/td]
FROM #Sample
WHERE IsActive =1
FOR XML PATH('tr')) AS varchar(MAX));SET @HTMLTable = REPLACE(REPLACE(@HTMLTable, '>', '>'), '<', '<'); --Remove Escape Characters
--Use sp_send_dbmail on msdb.
EXEC msdb.dbo.sp_send_dbmail @profile_name = ..., @body = @HTMLTable, ...;GO
DROP TABLE #Sample;
GO
Thom, that's cool. Is it possible to do red font for partial data in a cell. For example if in this result set I want to add a column like "RevenueAmt Min/Max" where value should be like '12,345/(12,345)". The bracket means negative value and should be shown in red. The two values come from separate columns in same table and have to b merged to one column in HTML table.
July 18, 2017 at 5:20 am
Lowell - Monday, July 17, 2017 6:27 AMLowell, thanks but after converting it to money it contains those values post decimal. How can that be removed ? I want it in format 12,345 and not 12,345.00
in that case, since it's converted to a string, you could simply REPLACE '.00' with an empty string.REPLACE(CONVERT(varchar,CONVERT(money,@RevenueAmt),1),'.00','')
Lowell
July 18, 2017 at 5:31 am
sqlenthu 89358 - Tuesday, July 18, 2017 5:09 AMThom, that's cool. Is it possible to do red font for partial data in a cell. For example if in this result set I want to add a column like "RevenueAmt Min/Max" where value should be like '12,345/(12,345)". The bracket means negative value and should be shown in red. The two values come from separate columns in same table and have to b merged to one column in HTML table.
Yes, just encapsulate the part you want in red in the case expressions containing the font markup. Have a go, and if you don't have any luck, post back with what you've tried.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 18, 2017 at 8:15 am
sqlenthu 89358 - Tuesday, July 18, 2017 5:05 AMLowell - Monday, July 17, 2017 6:27 AMLowell, thanks but after converting it to money it contains those values post decimal. How can that be removed ? I want it in format 12,345 and not 12,345.00
in that case, since it's converted to a string, you could simply REPLACE '.00' with an empty string.REPLACE(CONVERT(varchar,CONVERT(money,@RevenueAmt),1),'.00','')
Awesome. It was simple. Thanks a lot. On the other hand sad why it didn't come to my mind.
July 19, 2017 at 1:42 am
Thom A - Tuesday, July 18, 2017 5:31 AMsqlenthu 89358 - Tuesday, July 18, 2017 5:09 AMThom, that's cool. Is it possible to do red font for partial data in a cell. For example if in this result set I want to add a column like "RevenueAmt Min/Max" where value should be like '12,345/(12,345)". The bracket means negative value and should be shown in red. The two values come from separate columns in same table and have to b merged to one column in HTML table.Yes, just encapsulate the part you want in red in the case expressions containing the font markup. Have a go, and if you don't have any luck, post back with what you've tried.
Hey Thom, I tried it as per your suggestion but the font tag comes in the output as is coded and not actually produces that effect.
July 19, 2017 at 1:46 am
sqlenthu 89358 - Wednesday, July 19, 2017 1:42 AMThom A - Tuesday, July 18, 2017 5:31 AMsqlenthu 89358 - Tuesday, July 18, 2017 5:09 AMThom, that's cool. Is it possible to do red font for partial data in a cell. For example if in this result set I want to add a column like "RevenueAmt Min/Max" where value should be like '12,345/(12,345)". The bracket means negative value and should be shown in red. The two values come from separate columns in same table and have to b merged to one column in HTML table.Yes, just encapsulate the part you want in red in the case expressions containing the font markup. Have a go, and if you don't have any luck, post back with what you've tried.
Hey Thom, I tried it as per your suggestion but the font tag comes in the output as is coded and not actually produces that effect.
Can you post what you tried?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 19, 2017 at 2:29 am
Thom A - Wednesday, July 19, 2017 1:46 AMsqlenthu 89358 - Wednesday, July 19, 2017 1:42 AMThom A - Tuesday, July 18, 2017 5:31 AMsqlenthu 89358 - Tuesday, July 18, 2017 5:09 AMThom, that's cool. Is it possible to do red font for partial data in a cell. For example if in this result set I want to add a column like "RevenueAmt Min/Max" where value should be like '12,345/(12,345)". The bracket means negative value and should be shown in red. The two values come from separate columns in same table and have to b merged to one column in HTML table.Yes, just encapsulate the part you want in red in the case expressions containing the font markup. Have a go, and if you don't have any luck, post back with what you've tried.
Hey Thom, I tried it as per your suggestion but the font tag comes in the output as is coded and not actually produces that effect.
Can you post what you tried?
Here is the query which I am currently using:
Select
July 19, 2017 at 2:34 am
sqlenthu 89358 - Wednesday, July 19, 2017 2:29 AMHere is the query which I am currently using:Select
case when RevAmtMax <0 then '<font color="red">' + '($'+convert(varchar,RevAmtMax) +')' + '</font>' else '$'+convert(varchar,RevAmtMax) end + '/ '
case when RevAmtMin <0 then '<font color="red">' + '($'+convert(varchar,RevAmtMin) +')' +'</font>' else '$'+convert(varchar,RevAmtMin) end
From #mytable
There should be more to your SQL than that, there should be several statements. For example, the replace statement to remove the decimal points, and the escape characters, as well as your variable and header declarations.. I'm assuming you've removed (some of) these, which is why your email is displaying '<font="red">', rather than changing the font to red.
Have a look at my original post, which shows how to replace the escape characters.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 19, 2017 at 4:17 am
Thom A - Wednesday, July 19, 2017 2:34 AMsqlenthu 89358 - Wednesday, July 19, 2017 2:29 AMHere is the query which I am currently using:Select
case when RevAmtMax <0 then '<font color="red">' + '($'+convert(varchar,RevAmtMax) +')' + '</font>' else '$'+convert(varchar,RevAmtMax) end + '/ '
case when RevAmtMin <0 then '<font color="red">' + '($'+convert(varchar,RevAmtMin) +')' +'</font>' else '$'+convert(varchar,RevAmtMin) end
From #mytableThere should be more to your SQL than that, there should be several statements. For example, the replace statement to remove the decimal points, and the escape characters, as well as your variable and header declarations.. I'm assuming you've removed (some of) these, which is why your email is displaying '<font="red">', rather than changing the font to red.
Have a look at my original post, which shows how to replace the escape characters.
Selectcase when RevAmtMax <0 then '<font color="red">' + '($'+replace(convert(varchar,convert(money,RevAmtMax,1), '.00','') +')' + '</font>' else '$'+ replace(convert(varchar,convert(money,RevAmtMax,1), '.00','') +')' end + '/ '
case when RevAmtMin <0 then '<font color="red">' + '($'+replace(convert(varchar,convert(money,RevAmtMax,1), '.00','') +')' +'</font>' else '$'+replace(convert(varchar,convert(money,RevAmtMax,1), '.00','') +')' end
From #mytable
July 19, 2017 at 4:30 am
sqlenthu 89358 - Wednesday, July 19, 2017 4:17 AMSelectcase when RevAmtMax <0 then '<font color="red">' + '($'+replace(convert(varchar,convert(money,RevAmtMax,1), '.00','') +')' + '</font>' else '$'+ replace(convert(varchar,convert(money,RevAmtMax,1), '.00','') +')' end + '/ '
case when RevAmtMin <0 then '<font color="red">' + '($'+replace(convert(varchar,convert(money,RevAmtMax,1), '.00','') +')' +'</font>' else '$'+replace(convert(varchar,convert(money,RevAmtMax,1), '.00','') +')' end
From #mytable
You've still excluded the replacement of the escape string (for example > (which represents greater than ( > ))), and there's no FOR XML PATH, so this isn;t going to work. You need to do this on your variable, as shown in my example above.
Have a look at the SQL I posted after your initial question. There are several statements in there, for example the creation of the table header, and the replace I mentioned above, but the above is only one. The whole process requires all of these steps to work. The bit that fixes the escape strings is commented as "--Remove Escape Characters".
if you don't understand a part of my SQL at the start, please ask. it's important that you understand the answers yo are provided, rather than just using it and then being unable to troubleshoot it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 24, 2017 at 9:12 pm
Thom A - Wednesday, July 19, 2017 4:30 AMsqlenthu 89358 - Wednesday, July 19, 2017 4:17 AMSelectcase when RevAmtMax <0 then '<font color="red">' + '($'+replace(convert(varchar,convert(money,RevAmtMax,1), '.00','') +')' + '</font>' else '$'+ replace(convert(varchar,convert(money,RevAmtMax,1), '.00','') +')' end + '/ '
case when RevAmtMin <0 then '<font color="red">' + '($'+replace(convert(varchar,convert(money,RevAmtMax,1), '.00','') +')' +'</font>' else '$'+replace(convert(varchar,convert(money,RevAmtMax,1), '.00','') +')' end
From #mytableYou've still excluded the replacement of the escape string (for example > (which represents greater than ( > ))), and there's no FOR XML PATH, so this isn;t going to work. You need to do this on your variable, as shown in my example above.
Have a look at the SQL I posted after your initial question. There are several statements in there, for example the creation of the table header, and the replace I mentioned above, but the above is only one. The whole process requires all of these steps to work. The bit that fixes the escape strings is commented as "--Remove Escape Characters".
if you don't understand a part of my SQL at the start, please ask. it's important that you understand the answers yo are provided, rather than just using it and then being unable to troubleshoot it.
Hey Thom. Thanks for your help. It worked perfectly. I actually missed the REPLACE command and that's why was getting issue. Sorry for late response as i was down with fever from past some days.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply