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-05 : 03:16:06

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')

i want a procedure without using cursors so that my ouptut is as below:(if possible i want the query using with operator)

Name:Arjun(24.5 Hrs spent)

jobname Time Date Comments
java work 4.5 9/26/2012 12:00:00 AM Sample test comments 1
sql work 10 9/28/2012 12:00:00 AM Sample test comments 2
.net work 7.5 8/13/2012 12:00:00 AM Sample test comments 3
php work 2.5 9/5/2012 12:00:00 AM Sample test comments 4

Name:Ravi(9 Hrs spent)

jobname time Date Comments
java work 5.5 9/14/2012 12:00:00 AM Sample test comments 1
.net work 3.5 8/24/2012 12:00:00 AM Sample test comments 2

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-10-05 : 04:23:49

not exactly the expected result , but close
this is better done in front application/report

select *

from(

select * from
(
SELECT name,jobid, jobname,[Date], comments, sum([time]) as [Hrs Spent]
FROM testjob
GROUP BY name,jobid, jobname,[Date], comments WITH ROLLUP)A
where name is not null and jobID is null

union all

select name, jobid, jobName, [date],comments,''
from testjob ) A

order by name,[date]

or

select A.name,A.[hrs spent] ,B.jobName,B.[time],B.[date],B.comments
from
(select name,sum(time) as [Hrs Spent]
from testjob
group by name) A
full join
(select jobname ,[time],[Date],comments ,name from testjob
)B on A.name=B.name

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-10-05 : 04:49:50

select *

from(

select name + ' (' + cast( [Hrs Spent] as varchar(30)) +' Hrs spent)' as txt
from
(
SELECT name,jobid, jobname,[Date], comments, sum([time]) as [Hrs Spent]
FROM testjob
GROUP BY name,jobid, jobname,[Date], comments WITH ROLLUP)A
where name is not null and jobID is null

union all

select name + ' ' + cast(jobid as varchar(30)) + ' ' + jobName +' '+ cast([date] as varchar(12)) + ' ' + comments
from testjob ) A

order by txt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources