| Author |
 Topic  |
|
|
mystifier
Starting Member
12 Posts |
 Posted - 06/17/2012 : 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. |
Edited by - mystifier on 06/17/2012 08:52:09
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/17/2012 : 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 - 06/17/2012 : 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
India
47173 Posts |
Posted - 06/17/2012 : 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 - 06/17/2012 : 14:17:48
|
Thanks visakh16,
That is running now. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/17/2012 : 15:03:00
|
wc
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Lowest Column Value Calculated Column
