467,917 Members | 1,408 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,917 developers. It's quick & easy.

Need help with MIN function

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?
Sep 24 '08 #1
  • viewed: 1061
1 Reply
Expert 512MB
I don't see data to help you better.

So try this:

1. Create Function:
Expand|Select|Wrap|Line Numbers
  1. Create function fGetMin(@A1 int, @A2 int, @A3 int, @A4 int, @A5 int)
  2. Returns int
  3. AS 
  4. BEGIN
  5. Declare @Res int
  6. Select @Res = min(Col)
  7. From (Select @A1 Col
  8.       Union
  9.       Select @A2
  10.       Union
  11.       Select @A3
  12.       Union 
  13.       Select @A4
  14.       Union  
  15.       Select @A5) Tmp
  16. Return @Res
  17. END
2. Execute your statement:

Expand|Select|Wrap|Line Numbers
  1. Select v.evalrecno, dbo.idtoname(evalteeid)as Faculty, 
  2. dbo.fGetMin (min(q1),min(q2),min(q3),min(q4),min(q5)), count(*) N 
  3. from viewr507 v, viewevalissue i, employee e 
  4. where v.evalrecno = I.recno 
  5. and e.eid = i.evalteeid 
  6. and acyear=2007
  7. and rotation between 1 and 12 
  8. group by v.evalrecno,evalteeid,acyear,q1,q2,q3,q4,q5 
  9. order by 2

Good Luck.
Sep 24 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by Jeff Wagner | last post: by
8 posts views Thread by JustSomeGuy | last post: by
7 posts views Thread by brian.digipimp | last post: by
7 posts views Thread by Timothy Shih | last post: by
7 posts views Thread by fox | last post: by
8 posts views Thread by | last post: by
2 posts views Thread by Anders B | last post: by
9 posts views Thread by pereges | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.