Each year we have to manual assign students to classes that were not included in original assignment either they are new or the class was cancelled etc, we are talking 100s of students where we have the course and the hour but not the classroom they should be in
the database has
Students stid, yr, sem, ..... Course yr, sem, crse, hr, cls, cap ... Student_Courses stid, yr, sem, crse, hr, cls (does not include those with no class assigned)
I managed to get all the students for the semester into a temp table to include those with no classroom assigned I also got all the classes and their current total and their max into another temp table
DECLARE @Crse TABLE (crse VARCHAR(8), hr smallint, cls smallint, cnt smallint, cap smallint) DECLARE @St2Cls TABLE (st_id CHAR(8), crse VARCHAR(8), hr smallint, cls smallint) INSERT @Crse VALUES ('math1', 1, 15, 8, 15), ('math1', 1, 16, 10, 25), ('math1', 2, 11, 11, 25), ('math1', 2, 10, 10, 25), ('math2', 1, 11, 7, 10), ('math2', 3, 15, 8, 15), ('math2', 3, 16, 9, 10), ('math2', 4, 10, 9, 10), ('math3', 2, 15, 11, 20), ('math3', 2, 16, 12, 20), ('math3', 2, 18, 13, 20), ('eng1', 1, 12, 6, 10), ('eng2', 2, 11, 7, 15), ('eng2', 2, 12, 8, 15), ('eng2', 3, 13, 9, 25), ('eng3', 2, 13, 10, 15), ('eng3', 2, 15, 12, 15), ('eng3', 2, 12, 13, 15) INSERT @St2Cls VALUES ('Y180750', 'math1', 1, 10) , ('Y180750', 'eng2', 2, null) , ('Y180750', 'math2', 3, 15) , ('Y180753', 'math2', 3, null) , ('Y180753', 'eng2', 2, 11) , ('Y180755', 'math1', 1, 10) , ('Y180755', 'math3', 2, null) , ('Y180757', 'eng1', 1, 12) , ('Y180757', 'math2', 3, 15) , ('Y180757', 'eng3', 2, 13) , ('Y180760', 'math1', 2, null) , ('Y180761', 'math2', 4, 10) , ('Y180762', 'math3', 2, 15) , ('Y180765', 'eng1', 1, 12) , ('Y180764', 'eng3', 2, null) SELECT a.st_id ,a.crse , a.hr into #tmp_st2cls_nul FROM @St2Cls a where a.cls is null ORDER BY a.crse , a.hr SELECT * FROM #tmp_st2cls_nul tnul
What I am trying to do now is go through
#tmp_st2cls_nul (again 100s or 1000s) and assign them to a class in @Crse based on the hour (hr), count (cnt) and capacity (cap) and add the results to another table, say St2Cls_final, which will have all the students for the semester with classes
My first thought was to do a for each
#tmp_st2cls_nul find the matching crse/hr and add to a classroom if cnt < cap
but then there will be classes with total equal to max and some with very little number of students.
I was looking at something like https://stackoverflow.com/questions/11589821/sql-i-need-to-divide-a-total-value-into-multiple-rows-in-another-table/11590272
that would register the student to the class based on how full it is, adding to the lowest cnt first so just doing a
IF cnt <= cap THEN insert into St2Cls_final values(st_id, crse, hr, cls);
would not work.
then I thought maybe if we go through
#tmp_st2cls_nul based on crs/hr and create a
#tmp_crse_hr table and go through
#tmp_st2cls_nul for each missing cls in
#tmp_crse_hr but was told that might be to resource intensive and add more to code since we would have to create a
#tmp_crse_hr for each null class go through
#tmp_st2cls_nul till there were none and start again with next null.
any thoughts please.
EDIT1: at times some course hours may have more students than the total max for each classroom therefore max/classroom is NOT as important as having as close to an equal amount in each classroom as possible. what we have done is added another table which gives us ONLY those courses that have a student with a null class
SELECT a.crse, a.hr, count(*) AS total into #tmp_crse_hr_nul FROM #tmp_st2cls_nul a GROUP BY a.crse, a.hr
So we have a count of how many times we need to go thru (loop!!) crse/hr in @Crse for a classroom
Just seem like we have too many tables and not much else going on. Any help appreciated.
EDIT 2: Ideally the output (St2Cls_final) would be
st_id crse hr cls Y180750 math1 1 10 Y180750 eng2 2 **11** Y180750 math2 3 15 Y180753 math2 3 **15** Y180753 eng2 2 11 Y180755 math1 1 10 Y180755 math3 2 **15** Y180757 eng1 1 12 Y180757 math2 3 15 Y180757 eng3 2 13 Y180760 math1 2 **10** Y180761 math2 4 10 Y180762 math3 2 15 Y180765 eng1 1 12 Y180764 eng3 2 **13**
assigning the student to the crse/hr/cls with the lowest cnt first