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:

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:

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.