| Author |
 Topic  |
|
|
tsaliki
Starting Member
India
11 Posts |
 Posted - 10/15/2012 : 02:46:17
|
I have a table
create table testjob
(
jobid int,
jobname varchar(100),
time float,
name varchar(50),
Date varchar(100),
comments varchar(500)
)
insert into testjob values ( 1001,'java work',4.5,'arjun','9/26/2012 12:00:00 AM','Sample test comments 1')
insert into testjob values ( 1005,'sql work',10,'arjun','9/28/2012 12:00:00 AM','Sample test comments 2')
insert into testjob values ( 1010,'.net work',7.5,'arjun','8/13/2012 12:00:00 AM','Sample test comments 3')
insert into testjob values ( 1040,'java work',5.5,'ravi','9/14/2012 12:00:00 AM','Sample test comments 1')
insert into testjob values ( 1023,'php work',2.5,'arjun','9/5/2012 12:00:00 AM','Sample test comments 4')
insert into testjob values ( 1027,'.net work',3.5,'ravi','8/24/2012 12:00:00 AM','Sample test comments 2')
and i Have a procedure
Create proc pr_newreport5
as
DECLARE @html nvarchar(max),@columns nvarchar(max), @table nvarchar(max);
SET @html='<!DOCTYPE html><html><head><title>Sample Test html report</title><style type="text/css">'+'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;}'+'</style></head><body>'
+'<table id=''table'' cellSpacing=''0'' cellPadding=''2'' width=''100%'' border=''0'' borderColorDark=''white'' borderColorLight=''#a0b4d7''>'
SET @columns='<TR><TD vAlign=''top'' align=''left'' class=''Heading'' bgcolor=''#4682B4'' colspan=''4''>Job Description</TD></TR>
<TR><TD vAlign=''middle'' align=''left'' class=''Heading'' bgcolor=''#4682B4''>Job Title</TD>
<TD vAlign=''middle'' align=''left'' class=''Heading'' bgcolor=''#4682B4''>Hours Spent</TD>
</TR>'
Select @table= CONVERT(nvarchar(max),
(SELECT td = rs.JobName
,''
,td = ISNULL(Cast(rs.time as varchar(10)), '')
FROM testjob AS rs
FOR XML PATH(N'tr'), TYPE));
SET @html = @html + +@columns +@table +
N'</table></body></html>';
select @table
select @html as testjob
The output i am getting is as below:
Job Description
Job Title Hours Spent
java work 4.5
sql work 10
.net work 7.5
java work 5.5
php work 2.5
.net work 3.5
Instead i want the output with the total time as well like below:
Job Description
Job Title Hours Spent
java work 4.5
sql work 10
.net work 7.5
java work 5.5
php work 2.5
.net work 3.5
Total 33.5
|
|
|
malpashaa
Constraint Violating Yak Guru
Saudi Arabia
257 Posts |
Posted - 10/15/2012 : 03:19:34
|
Replace the statement that start with;
SELECT @table = CONVERT(nvarchar(max),
with this:
SELECT @table = CONVERT(nvarchar(max), (SELECT td = rs.JobName, '' ,
td = ISNULL(Cast(rs.time as varchar(10)), '')
FROM (SELECT T.jobname, T.time
FROM testjob AS T
UNION ALL
SELECT 'Total', SUM(T.time)
FROM testjob AS T) AS rs
FOR XML PATH(N'tr'), TYPE));
For us, there is only the trying. The rest is not our business. ~T.S. Eliot
Muhammad Al Pasha |
 |
|
| |
Topic |
|
|
|
Sql server stored procedure query
