Compute Max of several on-the-fly columns

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)

Compute Max of several on-the-fly columns

Author

Topic

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-10-26 : 06:11:52

Hi all,

I have a stored procedure which creates some columns on the fly:
SELECT UserID, UserName, CostCenter,
CAST((My Calculation...) AS DECIMAL(3,1)) as 'Value1',
CAST((My Calculation...) AS DECIMAL(3,1)) as 'Value2',
....
from #TempAllValues

#TempAllValues only contains 1 single row. Currently I have a total of 15 different value-columns ('value1' - 'value15'). 'My calculation...' is of course a different calculation for each value. Now I want to add a MAX-column containing the maximum value of these columns, e.g.


Value1 - Value2 - Value3 - Value4 - Value5 - Max
44     - 41     - 46     - 44     - 43     - 46

I've found this tip here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906 and tried to use the first method but was not able to do it. I've added this:


	Max =     
	  	(
	  	select
	   		X1= max(bb.xx)
	  	from
	   		(
	   		select xx = #TempAllValues.Value1 where #TempAllValues.Value1 is not null union all
	   		select xx = #TempAllValues.Value2 where #TempAllValues.Value2 is not null union all
	   		select xx = #TempAllValues.Value3 where #TempAllValues.Value3 is not null union all
	   		select xx = #TempAllValues.Value4 where #TempAllValues.Value4 is not null
	   		) bb
	  	)

But I always get the error messages 'Value1 is not a valid column name', probably because it's generated on-the-fly. Is there nevertheless a way to do it?
Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 07:10:31

try something like this

;With CTE(UserID,UserName,CostCenter,Value1,Value2,..) AS
(
SELECT UserID, UserName, CostCenter, 
CAST((My Calculation...) AS DECIMAL(3,1)) as 'Value1',
CAST((My Calculation...) AS DECIMAL(3,1)) as 'Value2',
....
from #TempAllValues
)

SELECT MAX([Value])
FROM
(
SELECT Value1 AS [Value] FROM CTE
UNION ALL
SELECT Value2 FROM CTE
UNION ALL
....
)t

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-10-26 : 16:41:40

Hi visakh16,

this works

SELECT MAX([Value])
FROM
(
SELECT Value1 AS [Value] FROM CTE
)t

But as soon as I add 'UNION ALL' I could not save the SP: Incorrect Syntax near to ')'. Additionally, how could I also output the data I've computed before? Select * from CTE does not work...
Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 00:11:06

quote:
Originally posted by Heinz23

Hi visakh16,

this works
SELECT MAX([Value])
FROM
(
SELECT Value1 AS [Value] FROM CTE
)t

But as soon as I add 'UNION ALL' I could not save the SP: Incorrect Syntax near to ')'. Additionally, how could I also output the data I've computed before? Select * from CTE does not work...
Thanks!

why? you can use UNION ALL

SELECT MAX([Value])
FROM
(
SELECT Value1 AS [Value] FROM CTE
UNION ALL
SELECT Value2 AS [Value] FROM CTE
....
)t

the only reqmnt is that Value1,Value2,... should all be of same datatype.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources