CODEWITHSUNDEEP
sql-serverstored-procedures
Tsaliki
Tsaliki

Reputation: 11

Sql server Stored procedure help to execute the below query

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

Upvotes: 0

Views: 135

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Check out this SQL Fiddle

There is no need to use a WITH for your query.

select name,case when jobname is null
                 then cast(sum(time) as varchar(12)) + ' hrs spent'
                 else jobname end jobname,time,date,comments
from testjob
group by grouping sets ((name),(name,jobname,time,date,comments))
order by name,comments

You shouldn't really need the grouping set, I have only included it to show you how you can view it with summarized headers from within a query tool. If you are taking this data to a front-end tool like Visual Studio, you should control the grouping there and simply return the data in a sorted manner, e.g.

select name,jobname,time,date,comments
from testjob
order by name,comments
You keep a running total of time until the name changes.

FYI, your really should choose a better sort order, such as 'date' rather than 'comments', but this is the only order I found to give exactly the result you showed.

Upvotes: 3

Related Questions