Sfrgttk

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

Since the output is still not clear.

I have decided to show my output and ask about mismatch output.Output is not 100% correct.

SELECT sc.st_id, 

       sc.crse, 

       sc.hr, 

       CASE 

         WHEN ( sc.cls ) <= c.cls THEN ( sc.cls ) 

         ELSE c.cls 

       END 

--,sc.cls 

FROM   @Student2Class sc 

       CROSS apply(SELECT TOP 1 cls 

                   FROM   @Course c 

                   WHERE  sc.crse = c.crse 

                   AND sc.hr = c.hr --added amommy

                   ORDER  BY cnt ASC, 

                             cap DESC)c  

Why the output of following is so ?

Y180753     math2   3   **15**

Y180757     math2   1   15

Y180760     math1   2   **10**

what is the criteria of assigning class ?List all factors.