Need help with MIN function

teneesh

I'm building a report for my boss and he wants the report to display a field that has the minimum value of all minimal values for 5 min fields.
so for example,
when i run this query -
Select v.evalrecno, dbo.idtoname(evalteeid)as Faculty, min(q1)as min1,min(q2) as min2,min(q3) as min3,min(q4) as min4,min(q5) as min5, count(*) N from viewr507 v, viewevalissue i, employee e
where v.evalrecno = I.recno
and e.eid = i.evalteeid
and acyear=2007
and rotation between 1 and 12
group by v.evalrecno,evalteeid,acyear,q1,q2,q3,q4,q5
order by 2

and it brings me back each faculty's minimal scores per question (q1-q5), I need to pull the min from those. . . . is that possible?

iburyak

I don't see data to help you better.

So try this:

1. Create Function:

Expand|Select|Wrap|Line Numbers

 Create function fGetMin(@A1 int, @A2 int, @A3 int, @A4 int, @A5 int)

Returns int

AS 

BEGIN

Declare @Res int

Select @Res = min(Col)

From (Select @A1 Col

      Union

      Select @A2

      Union

      Select @A3

      Union 

      Select @A4

      Union  

      Select @A5) Tmp

Return @Res

END

2. Execute your statement:

Expand|Select|Wrap|Line Numbers

 Select v.evalrecno, dbo.idtoname(evalteeid)as Faculty, 

dbo.fGetMin (min(q1),min(q2),min(q3),min(q4),min(q5)), count(*) N 

from viewr507 v, viewevalissue i, employee e 

where v.evalrecno = I.recno 

and e.eid = i.evalteeid 

and acyear=2007

and rotation between 1 and 12 

group by v.evalrecno,evalteeid,acyear,q1,q2,q3,q4,q5 

order by 2

Good Luck.

Need help with MIN function

Post your reply

Similar topics