Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Sql server stored procedure query
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tsaliki
Starting Member

India
19 Posts

Posted - 10/05/2012 :  03:16:06  Show Profile  Reply with Quote
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

Romania
545 Posts

Posted - 10/05/2012 :  04:23:49  Show Profile  Reply with Quote
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

Edited by - stepson on 10/05/2012 04:28:25
Go to Top of Page

stepson
Aged Yak Warrior

Romania
545 Posts

Posted - 10/05/2012 :  04:49:50  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000