Lowest Column Value Calculated 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

General SQL Server Forums

New to SQL Server Programming

Lowest Column Value Calculated Column

Author

Topic

mystifier
Starting Member

12 Posts

Posted - 2012-06-17 : 08:48:20

Table1 Data: ID,kpi1,kpi2,kpi3,kpi4,kpi5
100,4,3,4,2,5
101,3,3,5,2,3
102,4,2,4,5,1
...

Query (to find lowest kpi score):
select ID, min(score) as Lowest from Table1
UNPIVOT (score for Col in (kpiid1,kpiid2,kpiid3,kpiid4,kpiid5)) as u
group by ID

Returns:
ID,Lowest
100,2
101,2
102,1
...

I want to add a 'LowestScore' calculated column but I can't figure out what needs to be there.

Many thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 11:10:12

do you mean this?


select t1.*,t2.Lowest 
from Table1 t1
inner join
(select ID, min(score) as Lowest from Table1 
UNPIVOT (score for Col in (kpiid1,kpiid2,kpiid3,kpiid4,kpiid5)) as u 
group by ID
)t2
ON t2.ID = t1.ID

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

mystifier
Starting Member

12 Posts

Posted - 2012-06-17 : 11:31:23

Hi Visakh16,

Yes, this is the query I have which works fine, but I wanted to simplify other queries by have a computed column 'Lowest' that has a single row value.

I can't get the actual formula right.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 11:40:05

wrap the logic in a udf and use it in computed column formula

like


CREATE FUNCTION GetLowestKPI
(
@ID int
)
RETURNS int
AS
BEGIN
DECLARE @MinKPI int

select @MinKPI=  min(score) 
from (SELECT * FROM  Table1 WHERE ID = @ID) t
UNPIVOT (score for Col in (kpiid1,kpiid2,kpiid3,kpiid4,kpiid5)) as u 
group by ID

RETURN (@MinKPI)

END


then create the computed column as

ALTER TABLE Table1 ADD Lowest AS dbo.GetLowestKPI(ID)

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

mystifier
Starting Member

12 Posts

Posted - 2012-06-17 : 14:17:48

Thanks visakh16,

That is running now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 15:03:00

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources