| Author |
 Topic  |
|
|
tsaliki
Starting Member
India
12 Posts |
 Posted - 10/05/2012 : 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
Yak Posting Veteran
Romania
95 Posts |
Posted - 10/05/2012 : 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 |
Edited by - stepson on 10/05/2012 04:28:25 |
 |
|
|
stepson
Yak Posting Veteran
Romania
95 Posts |
Posted - 10/05/2012 : 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 |
|
|
| |
Topic |
|
|
|
Sql server stored procedure query
