CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
LacOniC
LacOniC

Reputation: 944

Weekly Schedule with SQL

I need a weekly scheduler for students.

Can i create a query (matrix) like that just with SQL?

My current query is like that:

SELECT courseName, courseDay, courseStartHour, coursEndHour 
FROM courses WHERE studentId = 1

This is what i want:

enter image description here

I had that with Pivot:

enter image description here

Same hour can include multiple course. (max 2)

Note: We have Oracle 11g.

Sample (Simplified) Data:

CREATE TABLE StudentCourses ( courseCode varchar2(8) NOT NULL,  courseName varchar2(64) NOT NULL,  day number(10),  startHour number(10),  endHour number(10));

INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 352','Advertising Copywriting','1','9','11' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 352','Advertising Copywriting','1','11','13' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 332','Positioning Strategy in Advertising','2','9','12' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'COMM 324','Persuasion and Perception','2','14', '17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 312','Corporate Communications Practicum','3','14','17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 302','Strategic Media Planning','4','9','11' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 302','Strategic Media Planning','4','11','13' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 412','Case Studies in Advertising','4','13','15' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 411','Advertising Photography','4','14','16' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 412','Case Studies in Advertising','4','15','17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 411','Advertising Photography','4','16','18' );

Upvotes: 0

Views: 536

Answers (2)

D.J.
D.J.

Reputation: 386

Here is a simpler one.

WITH TEMP AS(SELECT LEVEL+7 AS HR  FROM DUAL CONNECT BY LEVEL <= 12)
SELECT TEMP.HR AS "HOUR",
       CASE WHEN TEMP.HR BETWEEN 9 AND 11 THEN 'X' ELSE NULL END AS MON,
       CASE WHEN TEMP.HR BETWEEN 11 AND 14 THEN 'X' ELSE NULL END AS TUE,
       NULL AS WED,
       NULL AS THU,
       NULL AS FRI,
       NULL AS SAT,
       NULL AS SUN
       FROM DUAL, TEMP

Upvotes: 2

hotfix
hotfix

Reputation: 3396

is that what you Need?

with tab as (
select 8 as "hour", null as monday, null as tuesday, null as wednesday, null as thursday, null as friday, null as saturday from dual union all
select 9 as "hour", 'x' as monday, null as tuesday, null as wednesday, null as thursday, null as friday, null as saturday  from dual union all
select 10 as "hour", 'x' as monday, null as tuesday, null as wednesday, null as thursday, null as friday, null as saturday  from dual union all
select 11 as "hour", 'x' as monday, 'x' as tuesday, null as wednesday, null as thursday, null as friday, null as saturday  from dual union all
select 12 as "hour", null as monday, 'x' as tuesday, null as wednesday, null as thursday, null as friday, null as saturday  from dual union all
select 13 as "hour", null as monday, 'x' as tuesday, null as wednesday, null as thursday, null as friday, null as saturday  from dual union all
select 14 as "hour", null as monday, 'x' as tuesday, null as wednesday, null as thursday, null as friday, null as saturday  from dual union all
select 15 as "hour", null as monday, null as tuesday, null as wednesday, null as thursday, null as friday, null as saturday  from dual union all
select 16 as "hour", null as monday, null as tuesday, null as wednesday, null as thursday, null as friday, null as saturday  from dual union all
select 17 as "hour", null as monday, null as tuesday, null as wednesday, null as thursday, null as friday, null as saturday  from dual union all
select 18 as "hour", null as monday, null as tuesday, null as wednesday, null as thursday, null as friday, null as saturday  from dual 
)
select * from tab

Upvotes: 1

Related Questions