Create a temp var for a select statement?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott Grosch - 18 Jul 2008 00:23 GMT

I'm doing repetative calculations in my select statement below:

select client, path,
str(kb/1024.0/1024.0,10,2) as 'GB',
str(elapsed/3600.0,10,2) as 'Hours',
str((kb/1024.0/1024.0)/(elapsed/3600.0),10,2) 'GB/Hour',
str(2016 / ((kb/1024.0/1024.0)/(elapsed/3600.0)),10,2) 'Hours do do 2 TB'
from v_fullRuns
where (kb/1024.0/1024.0) > 100 and (2016 / ((kb/1024.0/1024.0)/(elapsed/3600.0))) > 61 and start > '1/1/2008'
order by 6 desc

As you can see I'm doing the kb division five times and then I divide that by the elapsed/3600 a couple times. Is there a way to not list them so many times?

This isn't a permanent query, it's just something that I'm doing a couple times while data mining so don't want something heavy like a cursor

Reply to this Message

Eric Isaacs - 18 Jul 2008 00:55 GMT

> As you can see I'm doing the kb division five times and then I divide that by the elapsed/3600 a couple times. Is there a way to not list them so many times?

Not from what I'm guessing. You didn't provide any DDL for your table
(or view.) If it's a view, you could include this calculation as an
additional row in the view, then use that column instead of the
calculation in this query.

-Eric Isaacs

Reply to this Message

Plamen@sqlstudio.com - 18 Jul 2008 01:30 GMT

You can use a derived table (or a view) to define the calculations:

SELECT client, path,
STR(gb, 10, 2) AS 'GB',
STR(hours, 10, 2) AS 'Hours',
STR(gb / hours, 10, 2) AS 'GB/Hour',
STR( 2016 / (gb / hours), 10, 2) AS 'Hours do do 2 TB'
FROM (SELECT client, path,
kb/1024.0/1024.0 AS gb,
elapsed/3600.0 AS hours
FROM v_fullRuns
WHERE start > '20080101') AS T
WHERE gb > 100
AND (2016 / (gb / hours)) > 61
ORDER BY 6;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message