I have a table called student with the details up my challege with the storedprocedure is to be in position count the exits number of color based on the stream then assign a color using the code up.
The logic behind is if they are too many members with a color then it should adjust to use the color that has little member.
| NAME |
Color |
CLASS |
STREAM |
ADMNO |
| NAKYANZI PAULINE |
|
P7 |
E |
22-09008 |
| GGENZA MUKISA JORDAN |
Red |
P7 |
E |
17-06336 |
| MUKASA SETH EVANS |
Yellow |
P7 |
E |
16-05800 |
| KALIBBALA CARL MOSES |
Blue |
P7 |
E |
18-06529 |
| ATWIINE DONALD |
Yellow |
P7 |
E |
18-06725 |
| KAKALA TREASURE ETHAN JAPHETH |
Red |
P7 |
E |
18-06660 |
| MAKAILA ZOE KIRABO |
NULL |
P7 |
E |
23-09387 |
| TUHAIRWE ANDREW |
Yellow |
P7 |
E |
16-05827 |
| MBABAZI MAURICE MICHEAL |
Yellow |
P7 |
E |
17-06353 |
| KYOMUHENDO VANESSA |
Red |
P7 |
E |
16-05792 |
DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT
SET @Lower = 1 ---- The lowest random number
SET @Upper = 5 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
---SELECT @Random
truncate table colorsdata
insert into colorsdata(color)
select case when @random ='1' then 'Blue' else case when @random='2' then 'Green' else case when @random ='3' then 'Yellow' else case when @random ='4' then 'Red' end end end end
My query uses the Radom function to generate number between 1 to 4 and then I use these numbers to assign a color based on the Radom number provided to the student each student has a unique admno number that is permanent to that student.
So my question is how can i ensure that i first check the class to find out how many students that below to that color before assigning it. the reason for checking is i want to know if there are many students of that color that has been generated then i check the other colors to find which one has the less colors so that i assign it to that the essence of checking the colors is to balance them because i do not want the class to have very many students belonging to one color.
please help
