Calculate columns by adding the result of other coulmns on the fly

Question

Calculate columns by adding the result of other coulmns on the fly

schwizla

SSCrazy

Points: 2668
More actions
March 19, 2010 at 4:58 am

#99120

This is probably something simple but its frustrating me. I Have a Query to calculate data file sizes
select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,30),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a
i want to calculate the percentage free by adding another column which does "[PERCENT_FREE] = (100 - ([SPACE_USED_MB]/[FILE_SIZE_MB]*100))"
however I cannot just add the logic by referencing the column aliases
select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
[PERCENT_FREE] = (100 - ([SPACE_USED_MB]/[FILE_SIZE_MB]*100)), -- Doesn't Work
NAME = left(a.NAME,30),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a
I have to do the calculation by actually taking the columns values
select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
[PERCENT_FREE] = round(100-(((convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)))/(convert(decimal(12,2),round(a.size/128.000,2))))*100),2), -- Does Work!
NAME = left(a.NAME,30),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a
How can I make this work by just using the columns aliases as a reference and hence not performing unnecessary calculations twice?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 1

Divide'n'Conquer. Gather the data first, then manipulate it. It's a form of "pre-aggregation" and it can make for some very fast code because it can reduce the number of calculations used. It can also greatly simplify code if the calculations are really of a complex nature. The sub-query I used is called a "Derived Table". You can use a CTE in SQL Server 2005 and up for the same thing.

select

base.FILEID,

base.FILE_SIZE_MB,

base.SPACE_USED_MB,

base.FREE_SPACE_MB,

PERCENT_FREE = (100 - (base.SPACE_USED_MB/base.FILE_SIZE_MB*100.0)), --works now

base.NAME,

base.FILENAME

from

( --== Derived table gets the base data

select

a.FILEID,

FILE_SIZE_MB = convert(decimal(12,2),round(a.size/128.000,2)),

SPACE_USED_MB = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),

FREE_SPACE_MB = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,

NAME = left(a.NAME,30),

FILENAME = left(a.FILENAME,30)

from

dbo.sysfiles

) base

--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)

schwizla SSCrazy Points: 2668 More actions · Answer 2

Jeff, thanks for this. it works great!

In the end I opted for used a CTE as follows

WITH results (FILEID,FILE_SIZE_MB,SPACE_USED_MB,FREE_SPACE_MB, [NAME], [FILENAME])

AS

(SELECT

a.FILEID,

[FILE_SIZE_MB] =

CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)),

[SPACE_USED_MB] =

CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)),

[FREE_SPACE_MB] =

CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) ,

[NAME] = a.NAME,

[FILENAME] = a.FILENAME

FROM

dbo.sysfiles a

)

SELECT

FILEID,

[FILE_SIZE_MB],

[SPACE_USED_MB],

[FREE_SPACE_MB],

[PERCENT_FREE] = CONVERT(DECIMAL(12,1),ROUND((100 - ([SPACE_USED_MB]/[FILE_SIZE_MB]*100)),1)),

[NAME],

[FILENAME]

FROM results

That did the trick 🙂

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 3

Very cool. Thanks for the feedback.

Yep... since you have 2k5 or better (didn't know that because this is a 2k forum), a CTE does make it a little easier to understand. Well done and thanks for posting the solution you ended up using.

--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)

schwizla SSCrazy Points: 2668 More actions · Answer 4

Sorry bout the wrong section! I have found that some sections on the forums would be better as a generic section. I.e Replication and TSQL. Some sections have more visitors than others so you want your post to get the maximum coverage!

Will look around the forum and see if I can find a 'recommendations' section 🙂

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

dibbydibby (3/19/2010)
Sorry bout the wrong section! I have found that some sections on the forums would be better as a generic section. I.e Replication and TSQL. Some sections have more visitors than others so you want your post to get the maximum coverage!
Will look around the forum and see if I can find a 'recommendations' section 🙂

No problem. You'll also find that a lot of "us" monitor all new posts. We don't always have time to get to them but many of us look at "posts added today" under "Recent Posts" in the menu bar.

--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)