2

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

  • Your question seem nice but actually it is not so clear.Table name is not ok.Can you repeat your requirement point wise.Do you need all students who couldn't attend class ? Also those student who didn't complete their hours ? – KumarHarsh Dec 3 '18 at 6:13
  • I have updated the question, I hope that better explains. The nulls need to be cls from @Crse based on the lowest cnt (then max capacity cap, but that isn't as important as adding student to smallest class first) – Amommy Dec 3 '18 at 7:22
  • OK I think I see what you mean by table is not ok now. I have corrected @crse to have courses with same hr as it should be. For example there may be math3 at 1pm in 3 different classes (math3 hr -2 classes 15,16, 18) and class 15 has the least # of students add student Y180755 to class 15 – Amommy Dec 3 '18 at 8:03
  • Your question helped me answer a problem I was trying to solve myself. CHEERS – CrashWave Dec 3 '18 at 15:40
0

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.

  • I have corrected it to show the right class. This is great however the problem would be when a student is added to that class the cnt would change to cnt+1 for each st_id added to crse-hr-cls.A class that had only 5 students could end up with 50+ at the end and another would still have 7 or some other small cnt>5. – Amommy Dec 3 '18 at 10:11
  • @Amommy , can you explain this line onwards ,"Why the output of following is so ?.." – KumarHarsh Dec 3 '18 at 10:58
  • crse math2 hr 3 is taking place in two classrooms cls 15 &16 math2 3 15 8 15 math2 3 16 9 10 These are not the same EXACT classes happening at the same time, they may have different instructors or be selfstudy or online. classroom 15 has 8 students (cnt) and 16 has 9. therefore the next classroom to add a student to would be 15. then after that 15=16 with a count of 9. add another student to EITHER one, say 16, then 16=10cnt and 15=9cnt, then the next student would need to go to cls 15. Hope that helps :) – Amommy Dec 3 '18 at 11:17
  • @Amommy, can you throw sample and expected output for which my query is not working ? – KumarHarsh Dec 8 '18 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.