Query to calculate column values in a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query to calculate column values in a column

Author

Topic

tech1
Starting Member

49 Posts

Posted - 2010-06-23 : 12:06:57

so I have a query which will return values including from nullable fields from several tables.

now, finally, I want to add a column to the results where it does calculations (simple stuff) on specific fields. These fields however maybe NULL and if they are, then i dont want to do the calculation on them OR just return a default value for that.

The formula changes though depending on the "type" of report that is being requested by a parameter given in, or they have an option to generate all reports but still returning back all the columns even if they have null data in them.

how can I do this?

so a simple calc would be something like:

ColumnA / ColumnB
ColumnA / (ColumnD + ColumnC)

so I want the calculation value in a column including in the query.

thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-06-23 : 12:44:08

just use isnull to convert null to default value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-23 : 13:03:27

Not sure I fully follow what you want to do, but for the calculation you could use a CASE statement in conjuction with the paramter passed in. For example, if you had a paramter called @Param:

@Param INT


SELECT
	<column list>,
	CASE
		WHEN @Param = 1 THEN (ColumnA / ColumnB)
		WHEN @Param = 2 THEN (ColumnA / (ColumnD + ColumnC))
		ELSE 0
	END CalcColumn
FROM
	<Tables>

Depending on the data you could either COALESCE the Column values in the calcualtion or COALESCE the entire CASE statement.

tech1
Starting Member

49 Posts

Posted - 2010-06-23 : 14:33:09

Thanks.

its difficult to explain.

data in 1 table is being stored and the data is of "different types".

for certain "types" of data, I want to do calculations and show them in a column. But not all "types of data" will require calculations.

so given that (hopefully its easier to follow?) how can I achieve the goal?
the "type of data to return" is defined by the parameter coming into the SPROC.

I think the last poster post makes sense and may just work

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-23 : 15:15:07

A sample might help:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources