SQL Server Forums
Profile | Register | 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
 New Topic  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
Constraint Violating Yak Guru

Romania
425 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
Constraint Violating Yak Guru

Romania
425 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000