Dynamic parameter transfer in msdb.dbo.sp_send_dbmail
-
Hi all.
I have a problem with email sending via SQL Server. I need to count the number of minutes in the previous month of the running year.
The problem is that the e-mail should be sent every month and it should be determined dynamically (in April it should be for January, February, March). In March, for example, for January and February.
All this should be in tabular form.
I was able to make a working version, but it is not dynamic.
Could you help me?
DECLARE
@Message_Body NVARCHAR(MAX),
@subject VARCHAR(255),
@recipients VARCHAR(MAX),
@copy_recipients VARCHAR(MAX);
SELECT
@subject = 'Monthly Report',
@recipients = 'test@email.com',
@copy_recipients = 'test@email.com'
SELECT
@Message_Body =
N'<H2 STYLE="COLOR:BLACK">'+'Monthly Report'+ '' +
N'<table border="1">' +
N'<tr style="background-color: #DCDCDC; color: #000000"><th>User</th>'+
+'<th>'+(SELECT (DATENAME (month,DATEADD(M,-2,getdate()))+' ('+CAST(DATEPART(YEAR,DATEADD(M,-2,getdate())) AS VARCHAR(MAX))))+')</th>'
+'<th>'+(SELECT (DATENAME (month,DATEADD(M,-1,getdate()))+' ('+CAST(DATEPART(YEAR,DATEADD(M,-1,getdate())) AS VARCHAR(MAX))))+')</th>'
+N'<th>Total</th>'
+CAST ( (
SELECT ISNULL([User],'UNKNOWN') as td,''
,replace(replace(convert(varchar(20), cast(ISNULL([2],0)/60 as money), 1), ',', ','), '.00', '') as,''
,replace(replace(convert(varchar(20), cast(ISNULL([1],0)/60 as money), 1), ',', ','), '.00', '') as,''
FROM (
SELECT SUM(Duration) as Duration
,[Months]
,'Total Minutes' as [User]
FROM Table_Test
GROUP BY [Months]
) AS SourceTable
PIVOT(SUM(Duration) FOR [Months] IN ([1],[2],[3],[4],[5],[6],[7],[8])) AS PivotTable
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'+
N'<H2 STYLE="COLOR:BLACK">Thanks';
print @Message_Body
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@copy_recipients = @copy_recipients,
@subject = @subject,
@body = @Message_Body,
@body_format = 'HTML'
END- This topic was modified 3 years, 9 months ago by christi1711.
- This topic was modified 3 years, 9 months ago by christi1711.
-
You say minutes in the previous month, but then you list the three previous months and say it must be in tabular form.
Do you want each of the previous months' minutes separately? For example:
Mth Minutes
January #####
February #####
March #####
Or do you want the total of the three previous months?
What about January and February? Do you always start at January 1st or do you reach back into the prior year?
It always helps when you provide a short script to build some sample data in a table, and then show the expected output from the sample. It makes it easier for the volunteers here to look at your problem and develop solutions. Jeff Moden shows us how to do it right at https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help .
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
This should be the total number of minutes for each previous month separately and their total number. We do not return to the previous year, but start from the current year. That is, at the moment it is January 1, 2021.
For example, in April I expect to see such a notification:
in May I expect to see such a notification:
in June I expect to see such a notification:
That is, my code states that we need to go back two months. But I want to do it dynamically. So that when the date is read (it always be the first day of each month) , SQL predicts how many months ago it needs to return (if it's February, then one month, if December, then 11 months ago).
-
You don't need dynamic SQL to do this - you can build the table reference using FOR XML and if there are no values for specified months they won't be included.
To test this - I created a set of test data where we have a full years of data for User1 across 2 years (2020 and 2021) - and partial data for User2, User3 and User4. The first part gets the list of users and months to be reported - assuming that when run in January you want the prior years data. This will generate the results based on the beginning of the year through the end of the month prior to the run date - in other words, when run in March it will report January and February.
--==== Create some test data
Declare @tableTest Table (UserName varchar(30), MonthDate date, Duration int);
Insert Into @tableTest (UserName, MonthDate, Duration)
Values ('User1', '2019-12-15', 12)
, ('User1', '2020-01-15', 1), ('User1', '2020-02-15', 2), ('User1', '2020-03-15', 3), ('User1', '2020-04-15', 4), ('User1', '2020-05-15', 5), ('User1', '2020-06-15', 6)
, ('User1', '2020-01-16', 1), ('User1', '2020-02-16', 2), ('User1', '2020-03-16', 3), ('User1', '2020-04-16', 4), ('User1', '2020-05-16', 5), ('User1', '2020-06-16', 6)
, ('User1', '2020-07-15', 7), ('User1', '2020-08-15', 8), ('User1', '2020-09-15', 9), ('User1', '2020-10-15', 10), ('User1', '2020-11-15', 11), ('User1', '2020-12-15', 12)
, ('User1', '2021-01-15', 1), ('User1', '2021-02-15', 2), ('User1', '2021-03-15', 3), ('User1', '2021-04-15', 4), ('User1', '2021-05-15', 5), ('User1', '2021-06-15', 6)
, ('User1', '2021-07-15', 7), ('User1', '2021-08-15', 8), ('User1', '2021-09-15', 9), ('User1', '2021-10-15', 10), ('User1', '2021-11-15', 11), ('User1', '2021-12-15', 12)
, ('User2', '2020-01-15', 1), ('User2', '2020-02-15', 2), ('User3', '2020-03-15', 3), ('User3', '2020-04-15', 4), ('User4', '2020-05-15', 5), ('User4', '2020-06-15', 6)
, ('User2', '2020-07-15', 7), ('User2', '2020-08-15', 8), ('User3', '2020-09-15', 9), ('User3', '2020-10-15', 10), ('User4', '2020-11-15', 11), ('User4', '2020-12-15', 12)
, ('User2', '2021-01-15', 1), ('User2', '2021-02-15', 2), ('User3', '2021-03-15', 3), ('User3', '2021-04-15', 4), ('User4', '2021-05-15', 5), ('User4', '2021-06-15', 6)
, ('User2', '2021-07-15', 7), ('User2', '2021-08-15', 8), ('User3', '2021-09-15', 9), ('User3', '2021-10-15', 10), ('User4', '2021-11-15', 11), ('User4', '2021-12-15', 12);
--==== Solution
Set Nocount On;
Drop Table If Exists #results;
--==== Get todays (run) date and reporting year
Declare @runDate datetime = '2021-01-04'; -- getdate();
Declare @reportingYear int = year(@runDate) - iif(month(@runDate) = 1, 1, 0);
--==== Get results - include a row for every user for every month
With allUsers
As (
Select Distinct
tt.UserName
, MonthDate = datefromparts(@reportingYear, m.MonthValue, 1)
, m.MonthValue
, Duration = 0
From @tableTest tt
Cross Apply (Values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) As m(MonthValue)
Union All
Select tt.UserName
, tt.MonthDate
, MonthValue = datepart(month, tt.MonthDate)
, tt.Duration
From @tableTest tt
Where tt.MonthDate >= datefromparts(@reportingYear, 1, 1)
And tt.MonthDate < dateadd(month, datediff(month, 0, @runDate), 0)
)
Select UserName
, MonthDate
, MonthValue
, Duration
Into #results
From allUsers
Where MonthDate < dateadd(month, datediff(month, 0, @runDate), 0);
--==== Setup our variables
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 = cast(stuff((Select concat(h.MonthName, ' (', @reportingYear, ')')
From (Select Distinct
MonthName = datename(month, r.MonthDate)
, r.MonthValue
From #results r
) As h
Order By
h.MonthValue
For Xml Path('th'), elements), 1, 0, '<tr><th>User</th>') As varchar(max));
--==== Add Totals column and close the row
Set @tableHeader += '<th>Totals</th></tr>';
--==== Get the results as an xml table
Select @xmlResults = cast((Select UserName As td, ''
, pvt.[01] As td, ''
, pvt.[02] As td, ''
, pvt.[03] As td, ''
, pvt.[04] As td, ''
, pvt.[05] As td, ''
, pvt.[06] As td, ''
, pvt.[07] As td, ''
, pvt.[08] As td, ''
, pvt.[09] As td, ''
, pvt.[10] As td, ''
, pvt.[11] As td, ''
, pvt.[12] As td, ''
, coalesce(pvt.[01], 0) + coalesce(pvt.[02], 0) + coalesce(pvt.[03], 0)
+ coalesce(pvt.[04], 0) + coalesce(pvt.[05], 0) + coalesce(pvt.[06], 0)
+ coalesce(pvt.[07], 0) + coalesce(pvt.[08], 0) + coalesce(pvt.[09], 0)
+ coalesce(pvt.[10], 0) + coalesce(pvt.[11], 0) + coalesce(pvt.[12], 0) As td, ''
From (Select r.UserName
, r.MonthValue
, r.Duration
From #results r
) As d
Pivot (sum(d.Duration) For d.MonthValue In ([01], [02], [03], [04], [05], [06]
, [07], [08], [09], [10], [11], [12])) As pvt
Where coalesce(pvt.[01], 0) + coalesce(pvt.[02], 0) + coalesce(pvt.[03], 0)
+ coalesce(pvt.[04], 0) + coalesce(pvt.[05], 0) + coalesce(pvt.[06], 0)
+ coalesce(pvt.[07], 0) + coalesce(pvt.[08], 0) + coalesce(pvt.[09], 0)
+ coalesce(pvt.[10], 0) + coalesce(pvt.[11], 0) + coalesce(pvt.[12], 0) > 0
For Xml Path('tr'), elements) As varchar(max));
--==== Send Notification if we have results
If @xmlResults Is Not Null
Begin
--==== Uncomment this to set zero values to blank cells
--Set @xmlResults = replace(@xmlResults, '<td>0</td>', '<td></td>');
--==== Setup the CSS style of the message
Set @body = '<style type=''text/css''>';
Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
Set @body += 'td {font-size:10pt; text-align:center; border:1px DarkCyan solid; padding:2px 2px 2px 2px;}';
Set @body += 'th {font-size:10pt; text-align:center; padding:2px 2px 2px 2px; background-color:DarkCyan; color:White;}';
Set @body += 'name tr {color:Black; background-color:DarkCyan;}';
Set @body += '</style>'
--==== Setup the body of the message
Set @body += '<html><body> A paragraph of text here - to identify the table and/or results included
';
--==== Setup the table with the defined table header and XML results
Set @body += '<table style="margin-left:30px">' + @tableHeader + @xmlResults + '</table>';
--==== Close the body and html
Set @body += '</body></html>';
Select cast(@body As xml);
--==== Send the HTML formatted email message
-- Execute msdb.dbo.sp_send_dbmail
-- @profile_name = 'Your Public Profile Here'
-- , @from_address = 'SomeAddress@SomeDomain.com' -- This does not have to exist as a real address
-- , @reply_to = 'ReplyTo@Address.com' -- Can be a 'fake' address...but a real address should be used
-- , @recipients = @recipients
-- , @copy_recipients = @cc_recipients
-- , @subject = 'Place your subject here'
-- , @body_format = 'HTML'
-- , @body = @body;
End
GoYou can expand on this as needed - adjust the CSS style sheet to get the look and feel you want for the table.
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 -
Hi Jeffrey,
thanks a lot.
But the problem is that the data for the previous months exists. But I don't need them. I need data from the beginning of the year until today. And this today will change every month
- christi1711 wrote:
Hi Jeffrey, thanks a lot. But the problem is that the data for the previous months exists. But I don't need them. I need data from the beginning of the year until today. And this today will change every month
Well - obviously you didn't try the solution I posted. It does exactly what you were asking for - starting at the first of the year through the end of the month prior to current date (@runDate).
I made a few changes - this is a bit cleaner:
--==== Create some test data
Declare @tableTest Table (UserName varchar(30), MonthDate date, Duration int);
Insert Into @tableTest (UserName, MonthDate, Duration)
Values ('User1', '2019-12-15', 12)
, ('User1', '2020-01-15', 1), ('User1', '2020-02-15', 2), ('User1', '2020-03-15', 3), ('User1', '2020-04-15', 4), ('User1', '2020-05-15', 5), ('User1', '2020-06-15', 6)
, ('User1', '2020-01-16', 1), ('User1', '2020-02-16', 2), ('User1', '2020-03-16', 3), ('User1', '2020-04-16', 4), ('User1', '2020-05-16', 5), ('User1', '2020-06-16', 6)
, ('User1', '2020-07-15', 7), ('User1', '2020-08-15', 8), ('User1', '2020-09-15', 9), ('User1', '2020-10-15', 10), ('User1', '2020-11-15', 11), ('User1', '2020-12-15', 12)
, ('User1', '2021-01-15', 1), ('User1', '2021-02-15', 2), ('User1', '2021-03-15', 3), ('User1', '2021-04-15', 4), ('User1', '2021-05-15', 5), ('User1', '2021-06-15', 6)
, ('User1', '2021-07-15', 7), ('User1', '2021-08-15', 8), ('User1', '2021-09-15', 9), ('User1', '2021-10-15', 10), ('User1', '2021-11-15', 11), ('User1', '2021-12-15', 12)
, ('User2', '2020-01-15', 1), ('User2', '2020-02-15', 2), ('User3', '2020-03-15', 3), ('User3', '2020-04-15', 4), ('User4', '2020-05-15', 5), ('User4', '2020-06-15', 6)
, ('User2', '2020-07-15', 7), ('User2', '2020-08-15', 8), ('User3', '2020-09-15', 9), ('User3', '2020-10-15', 10), ('User4', '2020-11-15', 11), ('User4', '2020-12-15', 12)
, ('User2', '2021-01-15', 1), ('User2', '2021-02-15', 2), ('User3', '2021-03-15', 3), ('User3', '2021-04-15', 4), ('User4', '2021-05-15', 5), ('User4', '2021-06-15', 6)
, ('User2', '2021-07-15', 7), ('User2', '2021-08-15', 8), ('User3', '2021-09-15', 9), ('User3', '2021-10-15', 10), ('User4', '2021-11-15', 11), ('User4', '2021-12-15', 12);
--==== Solution
Set Nocount On;
Drop Table If Exists #results;
--==== Get todays (run) date and start date
Declare @runDate date = '2021-03-04'; -- getdate();
--Declare @startDate date = dateadd(day, 1, eomonth(@runDate, -13)); -- Use this for rolling 12 months report
Declare @startDate date = datefromparts(year(@runDate) - iif(month(@runDate) = 1, 1, 0), 1, 1); -- Use this for YTD report
--==== Get results - include a row for every user for every month
With allUsers
As (
Select Distinct
tt.UserName
, MonthDate = dateadd(month, m.MonthValue - 1, @startDate)
, m.MonthValue
, Duration = 0
From @tableTest tt
Cross Apply (Values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) As m(MonthValue)
Union All
Select tt.UserName
, tt.MonthDate
, MonthValue = datediff(month, @startDate, tt.MonthDate) + 1
, tt.Duration
From @tableTest tt
Where tt.MonthDate >= @startDate
And tt.MonthDate < dateadd(day, 1, eomonth(@runDate, -1))
)
Select UserName
, MonthDate
, MonthValue
, Duration
Into #results
From allUsers
Where MonthDate < dateadd(day, 1, eomonth(@runDate, -1));
--==== Setup our variables
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 = cast(stuff((Select concat(h.MonthName, ' (', h.YearName, ')')
From (Select Distinct
MonthName = datename(month, r.MonthDate)
, YearName = datepart(year, r.MonthDate)
, r.MonthValue
From #results r
) As h
Order By
h.MonthValue
For Xml Path('th'), elements), 1, 0, '<tr><th>User</th>') As varchar(max));
--==== Add Totals column and close the row
Set @tableHeader += '<th>Totals</th></tr>';
--==== Get the results as an xml table
Select @xmlResults = cast((Select UserName As td, ''
, pvt.[01] As td, ''
, pvt.[02] As td, ''
, pvt.[03] As td, ''
, pvt.[04] As td, ''
, pvt.[05] As td, ''
, pvt.[06] As td, ''
, pvt.[07] As td, ''
, pvt.[08] As td, ''
, pvt.[09] As td, ''
, pvt.[10] As td, ''
, pvt.[11] As td, ''
, pvt.[12] As td, ''
, coalesce(pvt.[01], 0) + coalesce(pvt.[02], 0) + coalesce(pvt.[03], 0)
+ coalesce(pvt.[04], 0) + coalesce(pvt.[05], 0) + coalesce(pvt.[06], 0)
+ coalesce(pvt.[07], 0) + coalesce(pvt.[08], 0) + coalesce(pvt.[09], 0)
+ coalesce(pvt.[10], 0) + coalesce(pvt.[11], 0) + coalesce(pvt.[12], 0) As td, ''
From (Select r.UserName
, r.MonthValue
, r.Duration
From #results r
) As d
Pivot (sum(d.Duration) For d.MonthValue In ([01], [02], [03], [04], [05], [06]
, [07], [08], [09], [10], [11], [12])) As pvt
Where coalesce(pvt.[01], 0) + coalesce(pvt.[02], 0) + coalesce(pvt.[03], 0)
+ coalesce(pvt.[04], 0) + coalesce(pvt.[05], 0) + coalesce(pvt.[06], 0)
+ coalesce(pvt.[07], 0) + coalesce(pvt.[08], 0) + coalesce(pvt.[09], 0)
+ coalesce(pvt.[10], 0) + coalesce(pvt.[11], 0) + coalesce(pvt.[12], 0) > 0
For Xml Path('tr'), elements) As varchar(max));
--==== Send Notification if we have results
If @xmlResults Is Not Null
Begin
--==== Uncomment this to set zero values to blank cells
--Set @xmlResults = replace(@xmlResults, '<td>0</td>', '<td></td>');
--==== Setup the CSS style of the message
Set @body = '<style type=''text/css''>';
Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
Set @body += 'td {font-size:10pt; text-align:center; border:1px DarkCyan solid; padding:2px 2px 2px 2px;}';
Set @body += 'th {font-size:10pt; text-align:center; padding:2px 2px 2px 2px; background-color:DarkCyan; color:White;}';
Set @body += 'name tr {color:Black; background-color:DarkCyan;}';
Set @body += '</style>'
--==== Setup the body of the message
Set @body += '<html><body> A paragraph of text here - to identify the table and/or results included
';
--==== Setup the table with the defined table header and XML results
Set @body += '<table style="margin-left:30px">' + @tableHeader + @xmlResults + '</table>';
--==== Close the body and html
Set @body += '</body></html>';
Select cast(@body As xml);
--==== Send the HTML formatted email message
-- Execute msdb.dbo.sp_send_dbmail
-- @profile_name = 'Your Public Profile Here'
-- , @from_address = 'SomeAddress@SomeDomain.com' -- This does not have to exist as a real address
-- , @reply_to = 'ReplyTo@Address.com' -- Can be a 'fake' address...but a real address should be used
-- , @recipients = @recipients
-- , @copy_recipients = @cc_recipients
-- , @subject = 'Place your subject here'
-- , @body_format = 'HTML'
-- , @body = @body;
End
GoHere is the HTML that is generated when run for @runDate = getdate()
<style type="text/css">{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}td {font-size:10pt; text-align:center; border:1px DarkCyan solid; padding:2px 2px 2px 2px;}th {font-size:10pt; text-align:center; padding:2px 2px 2px 2px; background-color:DarkCyan; color:White;}name tr {color:Black; background-color:DarkCyan;}</style>
<html>
<body>
A paragraph of text here - to identify the table and/or results included
<table style="margin-left:30px">
<tr>
<th>User</th>
<th>January (2021)</th>
<th>February (2021)</th>
<th>Totals</th>
</tr>
<tr>
<td>User1</td>
<td>1</td>
<td>2</td>
<td>3</td>
</tr>
<tr>
<td>User2</td>
<td>1</td>
<td>2</td>
<td>3</td>
</tr>
</table>
</body>
</html>And here is the generated HTML for @runDate = '2021-01-04'
<style type="text/css">{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}td {font-size:10pt; text-align:center; border:1px DarkCyan solid; padding:2px 2px 2px 2px;}th {font-size:10pt; text-align:center; padding:2px 2px 2px 2px; background-color:DarkCyan; color:White;}name tr {color:Black; background-color:DarkCyan;}</style>
<html>
<body>
A paragraph of text here - to identify the table and/or results included
<table style="margin-left:30px">
<tr>
<th>User</th>
<th>January (2020)</th>
<th>February (2020)</th>
<th>March (2020)</th>
<th>April (2020)</th>
<th>May (2020)</th>
<th>June (2020)</th>
<th>July (2020)</th>
<th>August (2020)</th>
<th>September (2020)</th>
<th>October (2020)</th>
<th>November (2020)</th>
<th>December (2020)</th>
<th>Totals</th>
</tr>
<tr>
<td>User1</td>
<td>2</td>
<td>4</td>
<td>6</td>
<td>8</td>
<td>10</td>
<td>12</td>
<td>7</td>
<td>8</td>
<td>9</td>
<td>10</td>
<td>11</td>
<td>12</td>
<td>99</td>
</tr>
<tr>
<td>User2</td>
<td>1</td>
<td>2</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>7</td>
<td>8</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>18</td>
</tr>
<tr>
<td>User3</td>
<td>0</td>
<td>0</td>
<td>3</td>
<td>4</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>9</td>
<td>10</td>
<td>0</td>
<td>0</td>
<td>26</td>
</tr>
<tr>
<td>User4</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>5</td>
<td>6</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>11</td>
<td>12</td>
<td>34</td>
</tr>
</table>
</body>
</html>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 -
Hi all,
yes, I got it. Thanks.
I only try to remove users and group only by date
- christi1711 wrote:
Hi all,
yes, I got it. Thanks. I only try to remove users and group only by date
I don't understand - your original query had the user - but now you don't want the user? Only a single row in a table with just the dates? Or something else?
If you don't want to include the user - that is simple. Just remove the user column from the table header and xml results.
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 -
Jeffrey,
sorry I confused you.
it's just a fake value that needs to be written on the side (as select 'user').
That is the look should be:
-
This is what you had in your original query:
SELECT ISNULL([User],'UNKNOWN') as td,''
That is not a 'dummy' value...but if you really just want a dummy value - then use this:
--==== Get the results as an xml table
Select @xmlResults = cast((Select 'Report' As td, ''
, pvt.[01] As td, ''
, pvt.[02] As td, ''
, pvt.[03] As td, ''
, pvt.[04] As td, ''
, pvt.[05] As td, ''
, pvt.[06] As td, ''
, pvt.[07] As td, ''
, pvt.[08] As td, ''
, pvt.[09] As td, ''
, pvt.[10] As td, ''
, pvt.[11] As td, ''
, pvt.[12] As td, ''
, coalesce(pvt.[01], 0) + coalesce(pvt.[02], 0) + coalesce(pvt.[03], 0)
+ coalesce(pvt.[04], 0) + coalesce(pvt.[05], 0) + coalesce(pvt.[06], 0)
+ coalesce(pvt.[07], 0) + coalesce(pvt.[08], 0) + coalesce(pvt.[09], 0)
+ coalesce(pvt.[10], 0) + coalesce(pvt.[11], 0) + coalesce(pvt.[12], 0) As td, ''
From (Select r.MonthValue
, r.Duration
From #results r
) As d
Pivot (sum(d.Duration) For d.MonthValue In ([01], [02], [03], [04], [05], [06]
, [07], [08], [09], [10], [11], [12])) As pvt
For Xml Path('tr'), elements) As varchar(max));When building the temp table - just make sure you have a date value for every date within the range and that you have the columns MonthDate, MonthValue and Duration.
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 -
A single query can't return a variable number of columns. It would have to reflect one column for every month of the year and a total at the end.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills - The Dixie Flatline wrote:
A single query can't return a variable number of columns. It would have to reflect one column for every month of the year and a total at the end.
You should try my 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 -
Jeffrey,
Yes, it works great. I'm just interested in the question of how you can not group for each user a specific amount, but simply display the total number?
Sorry to confuse you, just the source code is not mine, and I do not understand html. And I would like to use the styles that I have in the script, but for some reason I can't change anything.
Your first option works great, but I wouldn't want to group by each user. I would like to add a column with a specific name (for example, Users) and calculate their total number for each month
-
I don't understand what results you want. Please provide sample data and expected results.
If you have a question on the code, then show which code and your question.
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 -
Jeffrey,
I expected to see such table (with only two rows):
I don't want to see a lot of rows for user. I expected to see duration in general (for the users in specific period). For example, in January we have user 1 - 5, User 2 - 6, User 3 - 7. I expected to see value 18 in January column.
And total minutes - it's only a dummy name of rows... I have user column in the table, but as I mention would like to see only the name of the column (not specific name of the users)...
As well, I would like to see such style for table:
@Message_Body =
N'<H2 STYLE="COLOR:BLACK">'+'Monthly Report'+ '' +
N'<table border="1">' +
N'<tr style="background-color: #DCDCDC; color: #000000"><th>User</th>'+
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply