x

Sql server Stored procedure 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
more ▼

asked Oct 05 '12 at 07:15 AM in Default

tsaliki gravatar image

tsaliki
150 8 10 12

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

TSQL wont give you great formatting on this, for that you are better using your UI to do the maths and then format the result - SSRS will calculate group totals and so on with great style and simplicity.

In TSQL you could use the follow to give roughly what you suggest:

 SELECT [t].[jobid] ,
  [t].[jobname] ,
  [t].[time] ,
  [t].[time] ,
  [t].[name] ,
  [t].[Date] ,
  [t].[comments]
 FROM [#testjob] AS t
 ORDER BY [t].[name]
 COMPUTE SUM([t].[time]) BY [t].[name]
more ▼

answered Oct 05 '12 at 07:44 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(comments are locked)
10|1200 characters needed characters left

As Jonathan said, you're better off using SSRS for the presentation part.

In case you'd like to get all the data you need in just one recordset, have a look at the following query:

select name, jobname, time, date, comments
 , SUM(time) over (partition by name) Time_SUM
from testjob

For more info on how to use the OVER clause to aggregate data, have a look at this article: http://blog.hoegaerden.be/2010/06/01/aggregating-data-with-the-over-clause/

BTW: it's not a good idea to name your columns "time" or "date". Those are data types nowadays. Even if you're currently on 2005, it's better to avoid them now so that you won't run into issues when upgrading.

more ▼

answered Oct 24 '12 at 08:46 AM

Valentino Vranken gravatar image

Valentino Vranken
1.5k 1 2 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x401
x7

asked: Oct 05 '12 at 07:15 AM

Seen: 795 times

Last Updated: Oct 24 '12 at 08:46 AM