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