Skip to Main Content
  • Questions
  • Find all free time in student course schedule

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Gerald.

Asked: March 05, 2020 - 3:11 pm UTC

Answered by: Connor McDonald - Last updated: March 24, 2020 - 1:15 am UTC

Category: SQL - Version: 12.1.0.2.0

Viewed 100+ times

You Asked

I have been asked to provide a list of the times a student is not in class by day of the week.

The tables involved look like this:

sfrstcr - this table contains the term code (SFRSTCR_TERM_CODE) and course number (SFRSTCR_CRN) that the student is registered for

ssrmeet - this table contains the term code (SSRMEET_TERM_CODE) and course number (SSRMEET_CRN) which match back to the SFRSTCR table
and also
the begin time (SSRMEET_BEGIN_TIME - example 0800), end time (SSRMEET_END_TIME - example 0850) and each day of the week it meets is a column
SSRMEET_SUN_DAY (would be 'U' if it meets on Sunday)
SSRMEET_MON_DAY (would be 'M' if it meets on Monday)
SSRMEET_TUE_DAY ('T' for Tuesday)
SSRMEET_WED_DAY ('W' for Wendeday)
SSRMEET_THU_DAY ('U' for Thursday)
SSRMEET_FRI_DAY ('F' for Friday)
SSRMEET_SAT_DAY ('S' for Saturday)

The reuest is a list of time that the student is not in class by day of the week and ignoring a 15 minute or less gap between classes because that is not enough time to matter


Additional information added below:

The two tables would look like this:

insert into sfrstcr (sfrstcr_term_code, sfrstcr_crn) values ('202020','23281');
insert into sfrstcr (sfrstcr_term_code, sfrstcr_crn) values ('202020','24279');
insert into sfrstcr (sfrstcr_term_code, sfrstcr_crn) values ('202020','24284');
insert into sfrstcr (sfrstcr_term_code, sfrstcr_crn) values ('202020','24302');
insert into sfrstcr (sfrstcr_term_code, sfrstcr_crn) values ('202020','24387');
insert into sfrstcr (sfrstcr_term_code, sfrstcr_crn) values ('202020','24883');


insert into ssrmeet
(ssrmeet_term_code, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, ssrmeet_sun_day, ssrmeet_mon_day, ssrmeet_tue_day, ssrmeet_wed_day, ssrmeet_thu_day, ssrmeet_fri_day, ssrmeet_sat_day)
values
('202020','23281','1000','1050',NULL,'M',NULL,'W',NULL,'F',NULL);


(ssrmeet_term_code, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, ssrmeet_sun_day, ssrmeet_mon_day, ssrmeet_tue_day, ssrmeet_wed_day, ssrmeet_thu_day, ssrmeet_fri_day, ssrmeet_sat_day)
values
('202020','24279','1600','1840',NULL,NULL,NULL,NULL,'R',NULL,NULL);

(ssrmeet_term_code, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, ssrmeet_sun_day, ssrmeet_mon_day, ssrmeet_tue_day, ssrmeet_wed_day, ssrmeet_thu_day, ssrmeet_fri_day, ssrmeet_sat_day)
values
('202020','24302','0800','0850',NULL,NULL,'T',NULL,NULL,NULL,NULL);

(ssrmeet_term_code, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, ssrmeet_sun_day, ssrmeet_mon_day, ssrmeet_tue_day, ssrmeet_wed_day, ssrmeet_thu_day, ssrmeet_fri_day, ssrmeet_sat_day)
values
('202020','24387','1200','1250',NULL,'M',NULL,'W',NULL,NULL,NULL);


(ssrmeet_term_code, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, ssrmeet_sun_day, ssrmeet_mon_day, ssrmeet_tue_day, ssrmeet_wed_day, ssrmeet_thu_day, ssrmeet_fri_day, ssrmeet_sat_day)
values
('202020','24387','1330','1610',NULL,NULL,NULL,NULL,NULL,'F',NULL);

(ssrmeet_term_code, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, ssrmeet_sun_day, ssrmeet_mon_day, ssrmeet_tue_day, ssrmeet_wed_day, ssrmeet_thu_day, ssrmeet_fri_day, ssrmeet_sat_day)
values
('202020','24883','1100','1215',NULL,'M',NULL,'W',NULL,NULL,NULL);


The goal being to take information from both tables where CRN and TERM CODE match and then calculate ALL of the times between 8:00 AM and 8:00 PM that the student is not in class by day of the week.

and we said...

Something like this perhaps

SQL>
SQL> create table sfrstcr ( sfrstcr_term_code varchar2(20), sfrstcr_crn varchar2(10));

Table created.

SQL> create table ssrmeet ( ssrmeet_term_code varchar2(20), ssrmeet_crn varchar2(10),
  2   ssrmeet_begin_time varchar2(10),
  3   ssrmeet_end_time varchar2(10),
  4   ssrmeet_sun_day varchar2(1),
  5   ssrmeet_mon_day varchar2(1),
  6   ssrmeet_tue_day varchar2(1),
  7   ssrmeet_wed_day varchar2(1),
  8   ssrmeet_thu_day varchar2(1),
  9   ssrmeet_fri_day varchar2(1),
 10   ssrmeet_sat_day varchar2(1)
 11   );

Table created.

SQL>
SQL>
SQL> insert into sfrstcr (sfrstcr_term_code, sfrstcr_crn) values ('202020','23281');

1 row created.

SQL> insert into sfrstcr (sfrstcr_term_code, sfrstcr_crn) values ('202020','24279');

1 row created.

SQL> insert into sfrstcr (sfrstcr_term_code, sfrstcr_crn) values ('202020','24284');

1 row created.

SQL> insert into sfrstcr (sfrstcr_term_code, sfrstcr_crn) values ('202020','24302');

1 row created.

SQL> insert into sfrstcr (sfrstcr_term_code, sfrstcr_crn) values ('202020','24387');

1 row created.

SQL> insert into sfrstcr (sfrstcr_term_code, sfrstcr_crn) values ('202020','24883');

1 row created.

SQL>
SQL>
SQL> insert into ssrmeet
  2  (ssrmeet_term_code, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, ssrmeet_sun_day, ssrmeet_mon_day, ssrmeet_tue_day, ssrmeet_wed_day, ssrmeet_thu_
day, ssrmeet_fri_day, ssrmeet_sat_day)
  3  values
  4  ('202020','23281','1000','1050',NULL,'M',NULL,'W',NULL,'F',NULL);

1 row created.

SQL>
SQL> insert into ssrmeet
  2  (ssrmeet_term_code, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, ssrmeet_sun_day, ssrmeet_mon_day, ssrmeet_tue_day, ssrmeet_wed_day, ssrmeet_thu_
day, ssrmeet_fri_day, ssrmeet_sat_day)
  3  values
  4  ('202020','24279','1600','1840',NULL,NULL,NULL,NULL,'R',NULL,NULL);

1 row created.

SQL>
SQL> insert into ssrmeet
  2  (ssrmeet_term_code, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, ssrmeet_sun_day, ssrmeet_mon_day, ssrmeet_tue_day, ssrmeet_wed_day, ssrmeet_thu_
day, ssrmeet_fri_day, ssrmeet_sat_day)
  3  values
  4  ('202020','24302','0800','0850',NULL,NULL,'T',NULL,NULL,NULL,NULL);

1 row created.

SQL>
SQL> insert into ssrmeet
  2  (ssrmeet_term_code, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, ssrmeet_sun_day, ssrmeet_mon_day, ssrmeet_tue_day, ssrmeet_wed_day, ssrmeet_thu_
day, ssrmeet_fri_day, ssrmeet_sat_day)
  3  values
  4  ('202020','24387','1200','1250',NULL,'M',NULL,'W',NULL,NULL,NULL);

1 row created.

SQL>
SQL> insert into ssrmeet
  2  (ssrmeet_term_code, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, ssrmeet_sun_day, ssrmeet_mon_day, ssrmeet_tue_day, ssrmeet_wed_day, ssrmeet_thu_
day, ssrmeet_fri_day, ssrmeet_sat_day)
  3  values
  4  ('202020','24387','1330','1610',NULL,NULL,NULL,NULL,NULL,'F',NULL);

1 row created.

SQL>
SQL> insert into ssrmeet
  2  (ssrmeet_term_code, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, ssrmeet_sun_day, ssrmeet_mon_day, ssrmeet_tue_day, ssrmeet_wed_day, ssrmeet_thu_
day, ssrmeet_fri_day, ssrmeet_sat_day)
  3  values
  4  ('202020','24883','1100','1215',NULL,'M',NULL,'W',NULL,NULL,NULL);

1 row created.

SQL>
SQL>
SQL> with unpiv as (
  2  SELECT *
  3  FROM   ssrmeet
  4  UNPIVOT (
  5   day_code FOR day_of_week IN (
  6   ssrmeet_sun_day as 'Sun',
  7   ssrmeet_mon_day as 'Mon',
  8   ssrmeet_tue_day as 'Tue',
  9   ssrmeet_wed_day as 'Wed',
 10   ssrmeet_thu_day as 'Thu',
 11   ssrmeet_fri_day as 'Fri',
 12   ssrmeet_sat_day as 'Sat'
 13   )
 14   )
 15  )
 16  select
 17    sfrstcr_term_code,
 18    sfrstcr_crn,
 19    unpiv.*
 20  from   sfrstcr,
 21         unpiv
 22  where  sfrstcr.sfrstcr_term_code =  unpiv.ssrmeet_term_code(+)
 23  and    sfrstcr.sfrstcr_crn =  unpiv.ssrmeet_crn(+)
 24  order by 1,2;

SFRSTCR_TERM_CODE    SFRSTCR_CR SSRMEET_TERM_CODE    SSRMEET_CR SSRMEET_BE SSRMEET_EN DAY D
-------------------- ---------- -------------------- ---------- ---------- ---------- --- -
202020               23281      202020               23281      1000       1050       Wed W
202020               23281      202020               23281      1000       1050       Mon M
202020               23281      202020               23281      1000       1050       Fri F
202020               24279      202020               24279      1600       1840       Thu R
202020               24284
202020               24302      202020               24302      0800       0850       Tue T
202020               24387      202020               24387      1330       1610       Fri F
202020               24387      202020               24387      1200       1250       Wed W
202020               24387      202020               24387      1200       1250       Mon M
202020               24883      202020               24883      1100       1215       Wed W
202020               24883      202020               24883      1100       1215       Mon M

11 rows selected.

SQL>
SQL>
SQL>
SQL>


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.