By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

Need help with MIN function

P: 46
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
Share this Question
Share on Google+
1 Reply


iburyak
Expert 100+
P: 1,017
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.