compute results

Question

Post reply

compute results

Zia Khan

Old Hand

Points: 341
More actions
April 6, 2006 at 2:12 am

#168909

Hi All,
Is there any way to declare variable for a computed results set or we can insert the results into temp table.
Thanks in advance
Zia

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Vee Hall of Fame Points: 3028 More actions · Answer 1

Vee

Hall of Fame

Points: 3028

April 6, 2006 at 2:59 am

#631153

Hi,

Can you provide some more explination

Daniela-267581 Ten Centuries Points: 1119 More actions · Answer 2

I hope I understood correctly: you can make the calculs and insert the result set into a temporary table using insert as for a usual table. Please write more about the problem

Daniela

Andreas AJV SSC Eights! Points: 800 More actions · Answer 3

Do you need to temporarily keep a dataset came by a query?

You can do this, using a temporary table:

CREATE TABLE #tempTable ( Value INT, i INT identity(1,1) )

USE myDB

GO

INSERT INTO #tempTable (Value)

SELECT (price*1.1) + 3

FROM Items

WHERE price IS NOT NULL and type = 'XXXX'

ORDER BY price ASC

------------
When you 've got a hammer, everything starts to look like a nail...

Zia Khan Old Hand Points: 341 More actions · Answer 4

Hi Daniela,

I have this query in which I am getting 2 results sets the second result is the compute values for the select statment.And my objective is to create a web job to display the both results in html but for some reason its showing me the results of select statments that way I ended up to use the #temptable

select distinct

operator Name,

convert(varchar(8),starttime,11) Date,

sum(KeyStrokes) Keys,

sum(ActKeyStrokes) ActKeys,

sum(NumOfLines) Lines,

sum(ActNumofLines) ActLines,

sum(actkeystrokes)/sum(actnumoflines) Keys_Line,

cast(((cast(sum(actkeystrokes) as real))/(cast(sum(keystrokes) as real))*100) as int) Accuracy

from

datacapstats

where

convert(varchar(8),starttime,11) > convert(varchar(8),getdate()-14,11)

and

convert(varchar(8),starttime,11) < convert(varchar(10),getdate(),11)

group by

operator,convert(varchar(8),starttime,11)

order by

operator,convert(varchar(8),starttime,11)

compute

avg(Sum(keystrokes)),

avg(sum(actkeystrokes)),

avg(cast(((cast(sum(actkeystrokes) as real))/(cast(sum(keystrokes) as real))*100) as int))

by

operator

If there is any way to make the both results set in html or what would be the nice way to solve this prob.......

Thanks

Zia

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Zia,

Instead of using COMPUTE, consider using WITH ROLLUP or WITH CUBE. There's some great documentation on how to exploit the related GROUPING sub-totals for ROLLUP and CUBE in Books OnLine under the documentation for WITH CUBE. That will allow the SELECT (and your compute formulas) to be returned as a single result set. And, no, these do not require OLAP.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Daniela-267581 Ten Centuries Points: 1119 More actions · Answer 6

Hi,

Yes, Jeff solution is the best for your problem

Daniela

Zia Khan Old Hand Points: 341 More actions · Answer 7

Thanks guys I have done that by using temp table ...

Best regards

Zia Khan

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

You really don't need a temptable for this. While it may work now, it's unnecessary much extra work for the server, and there is a slight chance that it may not work so well in the future. Please do have a look at the suggested WITH ROLLUP or WITH CUBE options before settling with a temptable solution. If they look strange to you, don't be afraid to ask.

/Kenneth