Revisions to Assign a student to a Classroom based on course and scheduled hour

Bumped by Community user

occurred Aug 21 at 16:07

Bumped by Community user

occurred Apr 22 at 16:01

Bumped by Community user

occurred Dec 23, 2023 at 16:08

Bumped by Community user

occurred Aug 25, 2023 at 6:08

Bumped by Community user

occurred Apr 23, 2023 at 19:06

Tweeted twitter.com/StackDBAs/status/1601864377375809536

occurred Dec 11, 2022 at 9:00

Bumped by Community user

occurred Dec 11, 2022 at 3:09

Bumped by Community user

occurred Aug 10, 2022 at 2:00

Bumped by Community user

occurred Apr 4, 2022 at 3:02

Bumped by Community user

occurred Dec 1, 2021 at 17:04

Bumped by Community user

occurred Aug 3, 2021 at 16:08

Bumped by Community user

occurred Apr 5, 2021 at 14:04

Bumped by Community user

occurred Dec 5, 2020 at 21:07

Bumped by Community user

occurred Aug 4, 2020 at 16:05

Bumped by Community user

occurred Apr 5, 2020 at 8:01

Bumped by Community user

occurred Dec 4, 2019 at 1:01

Bumped by Community user

occurred Aug 5, 2019 at 21:02

Bumped by Community user

occurred Apr 4, 2019 at 2:02

edited body

Source Link

edited Dec 3, 2018 at 9:13

Amommy

33
2

st_id   crse    hr  cls
Y180750     math1   1   10
Y180750     eng2    2   **11**
Y180750     math3math2   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**

st_id   crse    hr  cls
Y180750     math1   1   10
Y180750     eng2    2   **11**
Y180750     math3   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**

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**

edited body

Source Link

edited Dec 3, 2018 at 9:09

Amommy

33
2

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', 'math3''math2', 23, 15)
   , ('Y180753', 'math2', 3, null)
   , ('Y180753', 'eng2', 2, 11)
   , ('Y180755', 'math1', 21, 10)
   , ('Y180755', 'math3', 2, null)
   , ('Y180757', 'eng1', 1, 12)
   , ('Y180757', 'math2', 13, 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

st_id   crse    hr  cls
Y180750     math1   1   10
Y180750     eng2    2   **11**
Y180750     math3   23   15
Y180753     math2   3   **15**
Y180753     eng2    2   11
Y180755     math1   21   10
Y180755     math3   2   **15**
Y180757     eng1    1   12
Y180757     math2   13   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**

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', 'math3', 2, 15)
   , ('Y180753', 'math2', 3, null)
   , ('Y180753', 'eng2', 2, 11)
   , ('Y180755', 'math1', 2, 10)
   , ('Y180755', 'math3', 2, null)
   , ('Y180757', 'eng1', 1, 12)
   , ('Y180757', 'math2', 1, 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

st_id   crse    hr  cls
Y180750     math1   1   10
Y180750     eng2    2   **11**
Y180750     math3   2   15
Y180753     math2   3   **15**
Y180753     eng2    2   11
Y180755     math1   2   10
Y180755     math3   2   **15**
Y180757     eng1    1   12
Y180757     math2   1   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**

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

st_id   crse    hr  cls
Y180750     math1   1   10
Y180750     eng2    2   **11**
Y180750     math3   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**

added 2 characters in body

Source Link

edited Dec 3, 2018 at 8:04

Amommy

33
2

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', 'math3', 2, 15)
   , ('Y180753', 'math2', 3, null)
   , ('Y180753', 'eng2', 2, 11)
   , ('Y180755', 'math1', 2, 10)
   , ('Y180755', 'math3', 2, null)
   , ('Y180757', 'eng1', 1, 12)
   , ('Y180757', 'math2', 1, 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

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', 'math3', 2, 15)
   , ('Y180753', 'math2', 3, null)
   , ('Y180753', 'eng2', 2, 11)
   , ('Y180755', 'math1', 2, 10)
   , ('Y180755', 'math3', 2, null)
   , ('Y180757', 'eng1', 1, 12)
   , ('Y180757', 'math2', 1, 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

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', 'math3', 2, 15)
   , ('Y180753', 'math2', 3, null)
   , ('Y180753', 'eng2', 2, 11)
   , ('Y180755', 'math1', 2, 10)
   , ('Y180755', 'math3', 2, null)
   , ('Y180757', 'eng1', 1, 12)
   , ('Y180757', 'math2', 1, 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

added 54 characters in body

Source Link

edited Dec 3, 2018 at 7:59

Amommy

33
2

Loading

added 211 characters in body

Source Link

edited Dec 3, 2018 at 7:49

Amommy

33
2

Loading

added 650 characters in body

Source Link

edited Dec 3, 2018 at 7:19

Amommy

33
2

Loading

added 650 characters in body

Source Link

edited Dec 3, 2018 at 7:07

Amommy

33
2

Loading

Stack Exchange Network

Return to Question