Sql-server – Assign a student to a Classroom based on course and scheduled hour

sql servert-sql

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

Best Answer

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.

Related Solutions

Sql-server – Data Model For Summarizing Student Info

Calculate those counts on the fly.

For most data sets of this nature, I would imagine calculating the count of tardiness events would be relatively cheap. How many rows do you expect there to be in the long run in your students or attendance tables? Probably on the order of tens of thousands at most. Contrast that to calculating an account balance from its transaction history in database for a large bank with billions of transactions. In this case I would consider persisting a summary of these aggregations somewhere.

If you wanted to pre-calculate this data anyway, I would summarize the count of tardiness events per student per year in an indexed view as suggested in the top answer here.

For example:

CREATE VIEW dbo.tardiness_summary
WITH SCHEMABINDING
AS
SELECT
     student_id
   , year
   , COUNT_BIG(*) AS tardy_count
FROM dbo.attendance
GROUP BY 
     student_id
   , year
;

CREATE UNIQUE CLUSTERED INDEX IX_tardiness_summary
ON dbo.tardiness_summary (
     student_id
   , year
;

This is not a flexible approach, however, as this view is now schema-bound to the base tables and thus any modifications to either the view or the table will require rebuilding the view. Indexed views also have many restrictions on how they may be created or queried. Their advantage is that they guarantee the summary table will stay in sync with its sources because the database engine is now doing this work for you.

Sql-server – Prevent duplicate record counts in one to many query

SELECT DISTINCT is not the answer to the issue. You are right that the join is the problem. It produces multiple rows for the same student, when the student is connect to a teacher more than once.

One way to solve this is by using an EXISTS subquery:

SELECT Count(*) AS CountOfEthnicity, 
       s.Ethnicity
FROM dbo.StudentsSDRC AS s
WHERE EXISTS
      ( SELECT *
        FROM dbo.TeacherStudents AS t
        WHERE s.StudentNum = t.StudentNum
          AND t.teacherDCID = 43652
      )
GROUP BY s.Ethnicity ;

Other irrelevant to the problem notes / improvements:

The LEFT JOIN was acting as an INNER JOIN as one of the right table's columns was in the WHERE condition.
It's good to alias your tables with shorter names. Improves readability of the queries.
It's good to prefix the tables with the schema names (eg. dbo).
The pile of parentheses was not needed at all.

If you prefer the join for some reason, then this would work, too. I personally don't find it as explanatory as the 1st query.

It assumes that StudentNum is UNIQUE in table StudentsSDRC:

SELECT Count(DISTINCT s.StudentNum) AS CountOfEthnicity, 
       s.Ethnicity
FROM dbo.StudentsSDRC AS s
    JOIN dbo.TeacherStudents AS t
        ON s.StudentNum = t.StudentNum
WHERE t.teacherDCID = 43652
GROUP BY s.Ethnicity ;

Best Answer

Related Solutions

Sql-server – Data Model For Summarizing Student Info

Sql-server – Prevent duplicate record counts in one to many query

Related Question