| SQL |
SELECT ssbsect_crn,
ssbsect_term_code,
ssrblck_blck_code,
ssrmeet_start_date,
ssrmeet_end_date,
TO_CHAR(ssrmeet_start_date, 'DD-Mon-YYYY') AS start_date,
TO_CHAR(ssrmeet_end_date, 'DD-Mon-YYYY') AS end_date,
ssrmeet_begin_time,
ssrmeet_end_time,
TRUNC( TO_DATE(CONCAT(ssrmeet_begin_time,'00'),'HH24:MI:SS')) + ( ROUND( (TO_DATE(CONCAT(ssrmeet_begin_time,'00'),'HH24:MI:SS') - TRUNC(TO_DATE(CONCAT(ssrmeet_begin_time,'00'),'HH24:MI:SS')) ) * 96 ) / 96) as begin_time_round,
TRUNC( TO_DATE(CONCAT(ssrmeet_end_time,'00'),'HH24:MI:SS')) + ( ROUND( (TO_DATE(CONCAT(ssrmeet_end_time,'00'),'HH24:MI:SS') - TRUNC(TO_DATE(CONCAT(ssrmeet_end_time,'00'),'HH24:MI:SS')) ) * 96 ) / 96) as end_time_round,
DECODE(ssrmeet_sun_day, 'U', 'Sun ', '') ||
DECODE(ssrmeet_mon_day, 'M', 'Mon ', '') ||
DECODE(ssrmeet_tue_day, 'T', 'Tue ', '') ||
DECODE(ssrmeet_wed_day, 'W', 'Wed ', '') ||
DECODE(ssrmeet_thu_day, 'R', 'Thu ', '') ||
DECODE(ssrmeet_fri_day, 'F', 'Fri ', '') ||
DECODE(ssrmeet_sat_day, 'S', 'Sat ','') AS day_of_week,
ssrmeet_bldg_code,
ssrmeet_room_code,
sirasgn_pidm,
ssbsect_seats_avail,
ssbsect_wait_count,
ssbsect_voice_avail,
ssrmeet_schd_code,
stvschd_desc
FROM ssbsect
INNER JOIN stvschd ON
ssbsect_schd_code = stvschd_code
AND ssbsect_schd_code NOT IN ('PLA', 'CL', 'CB', 'CI', 'T')
LEFT OUTER JOIN ssrblck ON
ssrblck_term_code = ssbsect_term_code
AND ssrblck_crn = ssbsect_crn
LEFT OUTER JOIN ssrmeet ON
ssrmeet_term_code = ssbsect_term_code
AND ssrmeet_crn = ssbsect_crn
LEFT OUTER JOIN sirasgn ON
sirasgn_crn = ssbsect_crn
AND sirasgn_term_code = ssbsect_term_code
WHERE ssbsect.ssbsect_subj_code = 'JEWL'
AND ssbsect.ssbsect_crse_numb = '1112'
AND ssbsect.ssbsect_term_code >= 202040
AND ssbsect.ssbsect_ssts_code = 'A'
AND ssbsect.ssbsect_ptrm_end_date > sysdate-1
ORDER BY
ssrmeet_start_date, ssrmeet_begin_time, ssbsect_crn |
