Sql server stored procedure query

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)

Sql server stored procedure query

Author

Topic

tsaliki
Starting Member

19 Posts

Posted - 2012-10-15 : 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

264 Posts

Posted - 2012-10-15 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources