Sql server Stored procedure to execute the below query

Question

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

Answer 1

0

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]

answered Oct 05 '12 at 07:44 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

Answer 2

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.

Sql server Stored procedure to execute the below query

Follow this question

Related Questions