英文:
Creating a count per hour in SQL
问题
我尝试创建一个报告，使用课程时间表来计算在特定小时内有多少学生不可用。所以，如果一个学生从上午8:00到9:15有一堂课，我希望他们在8:00 - 9:00时间段和9:00 - 10:00时间段都被计为不可用。我正在使用SQL来编写这个报告。
我认为我需要一个CASE语句，其中包含逻辑，如果开始时间等于或大于8:00，并且结束时间大于8:00，则计数+1，但我不确定如何为持续超过一个小时的课程创建这个CASE语句，比如从7:30到15:30的一些课程。
注意：我希望将这些数据构建成柱状图，因此CASE语句的输出需要可读性以制作图表。
以下是我用来创建这个报告的数据示例：
select SGBSTDN.SGBSTDN_PIDM "PIDM",
SSRMEET.SSRMEET_SUN_DAY,
SSRMEET.SSRMEET_MON_DAY,
SSRMEET.SSRMEET_TUE_DAY,
SSRMEET.SSRMEET_WED_DAY,
SSRMEET.SSRMEET_THU_DAY,
SSRMEET.SSRMEET_FRI_DAY,
SSRMEET.SSRMEET_SAT_DAY,
SSRMEET.SSRMEET_BEGIN_TIME,
SSRMEET.SSRMEET_END_TIME
from SATURN.SGBSTDN SGBSTDN,
SATURN.SFRSTCR SFRSTCR,
SATURN.SSRMEET SSRMEET,
SATURN.SSBSECT SSBSECT,
SATURN.SGRSPRT SGRSPRT
where ( SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
and SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
and SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
and SGBSTDN.SGBSTDN_PIDM = SGRSPRT.SGRSPRT_PIDM
and SFRSTCR.SFRSTCR_TERM_CODE = SGRSPRT.SGRSPRT_TERM_CODE
and SSBSECT.SSBSECT_TERM_CODE = SSRMEET.SSRMEET_TERM_CODE
and SSBSECT.SSBSECT_CRN = SSRMEET.SSRMEET_CRN)
and ( SFRSTCR.SFRSTCR_TERM_CODE = '202330'
and SGRSPRT.SGRSPRT_ACTC_CODE = 'NCAASB'
--and :main_btn_RunQuery is not null
and SGRSPRT.SGRSPRT_ELIG_CODE = 'Y'
and SSBSECT.SSBSECT_SUBJ_CODE || SSBSECT.SSBSECT_CRSE_NUMB || SSBSECT.SSBSECT_SEQ_NUMB not like 'ES0%'
and SGBSTDN.SGBSTDN_TERM_CODE_EFF =
( select Max( N1.SGBSTDN_TERM_CODE_EFF ) "Max_SGBSTDN_TERM_CODE_EFF"
from SATURN.SGBSTDN N1
where N1.SGBSTDN_PIDM = SGBSTDN.SGBSTDN_PIDM ) )
order by SSRMEET.SSRMEET_BEGIN_TIME
英文:
I am trying to make a report that uses course schedules to count how many students are unavailable in a given hour. So if a student has a class from 8:00 to 9:15 am I want them to be counted as unavailable for the 8:00 - 9:00 time block and 9:00 - 10:00 time block. I am using SQL to write this report.
I believe I will need a case statement that builds in the logic of if the start time is equal to or greater than 8:00 and the end time is greater than 8:00 then count + 1 but I am unsure of how to create this case statement for classes that are over an hour such as some classes that go from 7:30 - 15:30.
Note: I am wanting to build this into a bar graph so the output of the case statement will need to be legible to a chart.
Here is an example of the data I am using to create this report.
Example Image
select SGBSTDN.SGBSTDN_PIDM "PIDM",
SSRMEET.SSRMEET_SUN_DAY,
SSRMEET.SSRMEET_MON_DAY,
SSRMEET.SSRMEET_TUE_DAY,
SSRMEET.SSRMEET_WED_DAY,
SSRMEET.SSRMEET_THU_DAY,
SSRMEET.SSRMEET_FRI_DAY,
SSRMEET.SSRMEET_SAT_DAY,
SSRMEET.SSRMEET_BEGIN_TIME,
SSRMEET.SSRMEET_END_TIME
from SATURN.SGBSTDN SGBSTDN,
SATURN.SFRSTCR SFRSTCR,
SATURN.SSRMEET SSRMEET,
SATURN.SSBSECT SSBSECT,
SATURN.SGRSPRT SGRSPRT
where ( SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
and SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
and SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
and SGBSTDN.SGBSTDN_PIDM = SGRSPRT.SGRSPRT_PIDM
and SFRSTCR.SFRSTCR_TERM_CODE = SGRSPRT.SGRSPRT_TERM_CODE
and SSBSECT.SSBSECT_TERM_CODE = SSRMEET.SSRMEET_TERM_CODE
and SSBSECT.SSBSECT_CRN = SSRMEET.SSRMEET_CRN)
and ( SFRSTCR.SFRSTCR_TERM_CODE = '202330'
and SGRSPRT.SGRSPRT_ACTC_CODE = 'NCAASB'
--and :main_btn_RunQuery is not null
and SGRSPRT.SGRSPRT_ELIG_CODE = 'Y'
and SSBSECT.SSBSECT_SUBJ_CODE || SSBSECT.SSBSECT_CRSE_NUMB || SSBSECT.SSBSECT_SEQ_NUMB not like 'ES0%'
and SGBSTDN.SGBSTDN_TERM_CODE_EFF =
( select Max( N1.SGBSTDN_TERM_CODE_EFF ) "Max_SGBSTDN_TERM_CODE_EFF"
from SATURN.SGBSTDN N1
where N1.SGBSTDN_PIDM = SGBSTDN.SGBSTDN_PIDM ) )
order by SSRMEET.SSRMEET_BEGIN_TIME
答案1
得分: 0
以下是翻译好的代码部分：
以下是逻辑，尽管需要针对 Oracle 和您的时间表记录进行更新。 这里的想法是创建一个包括所有小时0-23的结果集。 然后，您评估每个时间表记录与该结果集并计算那些学生不可用的记录。
;WITH HoursOfDay AS (
SELECT 0 AS HourOfDay
UNION ALL
SELECT HourOfDay + 1 FROM HoursOfDay WHERE HourOfDay + 1 < 24
)
SELECT h.HourOfDay,
CASE WHEN h.HourOfDay >= DATEPART(HOUR, SSRMEET_BEGIN_TIME) AND h.HourOfDay <= SSRMEET_END_TIME THEN 1 ELSE 0 END AS CountUnavailable
FROM HoursOfDay AS h
LEFT JOIN Schedule AS s ON 1 = 1
GROUP BY h.HourOfDay
ORDER BY h.HourOfDay
希望这能对您有所帮助。
英文:
Here is logic, though it needs updated for Oracle and your schedule record. The idea here is that you are creating a result set including all hours 0-23. You then evaluate every schedule record with that result set and count the ones where students are not available.
;WITH HoursOfDay AS (
SELECT 0 AS HourOfDay
UNION ALL
SELECT HourOfDay + 1 FROM HoursOfDay WHERE HourOfDay + 1 < 24
)
SELECT h.HourOfDay,
CASE WHEN h.HourOfDay >= DATEPART(HOUR, SSRMEET_BEGIN_TIME) AND h.HourOfDay <= SSRMEET_END_TIME THEN 1 ELSE 0 END AS CountUnavailable
FROM HoursOfDay AS h
LEFT JOIN Schedule AS s ON 1 = 1
GROUP BY h.HourOfDay
ORDER BY h.HourOfDay
答案2
得分: 0
以下是翻译好的代码部分：
如果您有示例数据：
CREATE TABLE classes (id, start_date, end_date) AS
SELECT 1, TIMESTAMP '2023-01-01 08:00:00', TIMESTAMP '2023-01-01 09:00:00' FROM DUAL UNION ALL
SELECT 2, TIMESTAMP '2023-01-01 07:30:00', TIMESTAMP '2023-01-01 15:30:00' FROM DUAL;
CREATE TABLE student_appointments (id, student_id, start_date, end_date) AS
SELECT 1, 1, TIMESTAMP '2023-01-01 08:00:00', TIMESTAMP '2023-01-01 09:00:00' FROM DUAL UNION ALL
SELECT 2, 2, TIMESTAMP '2023-01-01 07:30:00', TIMESTAMP '2023-01-01 15:30:00' FROM DUAL UNION ALL
SELECT 3, 3, TIMESTAMP '2023-01-01 16:00:00', TIMESTAMP '2023-01-01 17:00:00' FROM DUAL UNION ALL
SELECT 4, 4, TIMESTAMP '2023-01-01 07:00:00', TIMESTAMP '2023-01-01 07:30:00' FROM DUAL UNION ALL
SELECT 5, 4, TIMESTAMP '2023-01-01 15:30:00', TIMESTAMP '2023-01-01 17:00:00' FROM DUAL UNION ALL
SELECT 6, 5, TIMESTAMP '2023-01-01 07:00:00', TIMESTAMP '2023-01-01 17:00:00' FROM DUAL UNION ALL
SELECT 7, 6, TIMESTAMP '2023-01-01 12:00:00', TIMESTAMP '2023-01-01 13:00:00' FROM DUAL UNION ALL
SELECT 8, 6, TIMESTAMP '2023-01-01 14:00:00', TIMESTAMP '2023-01-01 15:00:00' FROM DUAL;
然后，您可以使用以下方式计算具有冲突预约的学生人数：
SELECT c.id AS class_id,
COUNT(DISTINCT s.student_id) AS num_students_unavailable
FROM classes c
INNER JOIN student_appointments s
ON (c.start_date < s.end_date AND s.start_date < c.end_date)
GROUP BY
c.id;
输出如下：
| CLASS_ID | NUM_STUDENTS_UNAVAILABLE |
|---|---|
| 2 | 4 |
| 1 | 3 |
英文:
If you have the sample data:
CREATE TABLE classes (id, start_date, end_date) AS
SELECT 1, TIMESTAMP '2023-01-01 08:00:00', TIMESTAMP '2023-01-01 09:00:00' FROM DUAL UNION ALL
SELECT 2, TIMESTAMP '2023-01-01 07:30:00', TIMESTAMP '2023-01-01 15:30:00' FROM DUAL;
CREATE TABLE student_appointments (id, student_id, start_date, end_date) AS
SELECT 1, 1, TIMESTAMP '2023-01-01 08:00:00', TIMESTAMP '2023-01-01 09:00:00' FROM DUAL UNION ALL
SELECT 2, 2, TIMESTAMP '2023-01-01 07:30:00', TIMESTAMP '2023-01-01 15:30:00' FROM DUAL UNION ALL
SELECT 3, 3, TIMESTAMP '2023-01-01 16:00:00', TIMESTAMP '2023-01-01 17:00:00' FROM DUAL UNION ALL
SELECT 4, 4, TIMESTAMP '2023-01-01 07:00:00', TIMESTAMP '2023-01-01 07:30:00' FROM DUAL UNION ALL
SELECT 5, 4, TIMESTAMP '2023-01-01 15:30:00', TIMESTAMP '2023-01-01 17:00:00' FROM DUAL UNION ALL
SELECT 6, 5, TIMESTAMP '2023-01-01 07:00:00', TIMESTAMP '2023-01-01 17:00:00' FROM DUAL UNION ALL
SELECT 7, 6, TIMESTAMP '2023-01-01 12:00:00', TIMESTAMP '2023-01-01 13:00:00' FROM DUAL UNION ALL
SELECT 8, 6, TIMESTAMP '2023-01-01 14:00:00', TIMESTAMP '2023-01-01 15:00:00' FROM DUAL;
Then you can count the number of students with conflicting appointments using:
SELECT c.id AS class_id,
COUNT(DISTINCT s.student_id) AS num_students_unavailable
FROM classes c
INNER JOIN student_appointments s
ON (c.start_date < s.end_date AND s.start_date < c.end_date)
GROUP BY
c.id;
Which outputs:
| CLASS_ID | NUM_STUDENTS_UNAVAILABLE |
|---|---|
| 2 | 4 |
| 1 | 3 |
答案3
得分: 0
这是用于回答原问题的完整代码，用于根据课程安排数据在给定日期内创建每个一小时时间块的计数。首先，我使用了一个CASE语句将每门课程的开始和结束时间分隔成一个小时的时间块。然后，我使用了一个NVL(SUM..)语句来计算每个时间块中的学生人数。一旦我有了这些数据，我使用了UNPIVOT函数来创建HOUR_COUNTS和HOUR_CODE变量，以在绘制数据时使用。
WITH TEMP_ATH_SCHEDULE AS
(SELECT ATHL_CRSE_CONF.GROUP_DAY_OF_WEEK,
NVL(SUM(ATHL_CRSE_CONF."1"), 0) AS Hour01,
NVL(SUM(ATHL_CRSE_CONF."2"), 0) AS Hour02,
NVL(SUM(ATHL_CRSE_CONF."3"), 0) AS Hour03,
NVL(SUM(ATHL_CRSE_CONF."4"), 0) AS Hour04,
NVL(SUM(ATHL_CRSE_CONF."5"), 0) AS Hour05,
NVL(SUM(ATHL_CRSE_CONF."6"), 0) AS Hour06,
NVL(SUM(ATHL_CRSE_CONF."7"), 0) AS Hour07,
NVL(SUM(ATHL_CRSE_CONF."8"), 0) AS Hour08,
NVL(SUM(ATHL_CRSE_CONF."9"), 0) AS Hour09,
NVL(SUM(ATHL_CRSE_CONF."10"), 0) AS Hour10,
NVL(SUM(ATHL_CRSE_CONF."11"), 0) AS Hour11,
NVL(SUM(ATHL_CRSE_CONF."12"), 0) AS Hour12,
NVL(SUM(ATHL_CRSE_CONF."13"), 0) AS Hour13,
NVL(SUM(ATHL_CRSE_CONF."14"), 0) AS Hour14,
NVL(SUM(ATHL_CRSE_CONF."15"), 0) AS Hour15,
NVL(SUM(ATHL_CRSE_CONF."16"), 0) AS Hour16,
NVL(SUM(ATHL_CRSE_CONF."17"), 0) AS Hour17,
NVL(SUM(ATHL_CRSE_CONF."18"), 0) AS Hour18,
NVL(SUM(ATHL_CRSE_CONF."19"), 0) AS Hour19,
NVL(SUM(ATHL_CRSE_CONF."20"), 0) AS Hour20,
NVL(SUM(ATHL_CRSE_CONF."21"), 0) AS Hour21,
NVL(SUM(ATHL_CRSE_CONF."22"), 0) AS Hour22,
NVL(SUM(ATHL_CRSE_CONF."23"), 0) AS Hour23,
NVL(SUM(ATHL_CRSE_CONF."0"), 0) AS Hour00
FROM (SELECT DISTINCT CASE
WHEN (TO_CHAR(TO_DATE('0000', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('0000', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0059', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('0000', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0059', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "0",
CASE
WHEN (TO_CHAR(TO_DATE('0100', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('0100', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0159', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('0100', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0159', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "1",
CASE
WHEN (TO_CHAR(TO_DATE('0200', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME
<details>
<summary>英文:</summary>
Here is the full code used to answer my original question of creating a count for each hour-long time block in a given day using course schedule data.
First I had to use a case statement that would separate the start and end times of each course into hour-long time blocks. Then I used an NVL(SUM..) statement to count the number of students in each time block.
Once I had that data I used an unpivot function to make variables HOUR_COUNTS and HOUR_CODE to be used when graphing the data.
WITH TEMP_ATH_SCHEDULE AS
(SELECT ATHL_CRSE_CONF.GROUP_DAY_OF_WEEK,
NVL(SUM(ATHL_CRSE_CONF."1"), 0) AS Hour01,
NVL(SUM(ATHL_CRSE_CONF."2"), 0) AS Hour02,
NVL(SUM(ATHL_CRSE_CONF."3"), 0) AS Hour03,
NVL(SUM(ATHL_CRSE_CONF."4"), 0) AS Hour04,
NVL(SUM(ATHL_CRSE_CONF."5"), 0) AS Hour05,
NVL(SUM(ATHL_CRSE_CONF."6"), 0) AS Hour06,
NVL(SUM(ATHL_CRSE_CONF."7"), 0) AS Hour07,
NVL(SUM(ATHL_CRSE_CONF."8"), 0) AS Hour08,
NVL(SUM(ATHL_CRSE_CONF."9"), 0) AS Hour09,
NVL(SUM(ATHL_CRSE_CONF."10"), 0) AS Hour10,
NVL(SUM(ATHL_CRSE_CONF."11"), 0) AS Hour11,
NVL(SUM(ATHL_CRSE_CONF."12"), 0) AS Hour12,
NVL(SUM(ATHL_CRSE_CONF."13"), 0) AS Hour13,
NVL(SUM(ATHL_CRSE_CONF."14"), 0) AS Hour14,
NVL(SUM(ATHL_CRSE_CONF."15"), 0) AS Hour15,
NVL(SUM(ATHL_CRSE_CONF."16"), 0) AS Hour16,
NVL(SUM(ATHL_CRSE_CONF."17"), 0) AS Hour17,
NVL(SUM(ATHL_CRSE_CONF."18"), 0) AS Hour18,
NVL(SUM(ATHL_CRSE_CONF."19"), 0) AS Hour19,
NVL(SUM(ATHL_CRSE_CONF."20"), 0) AS Hour20,
NVL(SUM(ATHL_CRSE_CONF."21"), 0) AS Hour21,
NVL(SUM(ATHL_CRSE_CONF."22"), 0) AS Hour22,
NVL(SUM(ATHL_CRSE_CONF."23"), 0) AS Hour23,
NVL(SUM(ATHL_CRSE_CONF."0"), 0) AS Hour00
FROM (SELECT DISTINCT CASE
WHEN (TO_CHAR(TO_DATE('0000', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('0000', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0059', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('0000', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0059', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "0",
CASE
WHEN (TO_CHAR(TO_DATE('0100', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('0100', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0159', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('0100', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0159', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "1",
CASE
WHEN (TO_CHAR(TO_DATE('0200', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('0200', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0259', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('0200', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0259', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "2",
CASE
WHEN (TO_CHAR(TO_DATE('0300', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('0300', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0359', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('0300', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0359', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "3",
CASE
WHEN (TO_CHAR(TO_DATE('0400', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('0400', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0459', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('0400', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0459', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "4",
CASE
WHEN (TO_CHAR(TO_DATE('0500', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('0500', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0559', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('0500', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0559', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "5",
CASE
WHEN (TO_CHAR(TO_DATE('0600', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('0600', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0659', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('0600', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0659', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "6",
CASE
WHEN (TO_CHAR(TO_DATE('0700', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('0700', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0759', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('0700', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0759', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "7",
CASE
WHEN (TO_CHAR(TO_DATE('0800', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('0800', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0859', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('0800', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0859', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "8",
CASE
WHEN (TO_CHAR(TO_DATE('0900', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('0900', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0959', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('0900', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('0959', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "9",
CASE
WHEN (TO_CHAR(TO_DATE('1000', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('1000', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1059', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('1000', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1059', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "10",
CASE
WHEN (TO_CHAR(TO_DATE('1100', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('1100', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1159', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('1100', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1159', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "11",
CASE
WHEN (TO_CHAR(TO_DATE('1200', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('1200', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1259', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('1200', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1259', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "12",
CASE
WHEN (TO_CHAR(TO_DATE('1300', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('1300', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1359', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('1300', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1359', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "13",
CASE
WHEN (TO_CHAR(TO_DATE('1400', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('1400', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1459', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('1400', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1459', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "14",
CASE
WHEN (TO_CHAR(TO_DATE('1500', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('1500', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1559', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('1500', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1559', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "15",
CASE
WHEN (TO_CHAR(TO_DATE('1600', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('1600', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1659', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('1600', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1659', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "16",
CASE
WHEN (TO_CHAR(TO_DATE('1700', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('1700', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1759', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('1700', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1759', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "17",
CASE
WHEN (TO_CHAR(TO_DATE('1800', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('1800', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1859', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('1800', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1859', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "18",
CASE
WHEN (TO_CHAR(TO_DATE('1900', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('1900', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1959', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('1900', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('1959', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "19",
CASE
WHEN (TO_CHAR(TO_DATE('2000', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('2000', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('2059', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('2000', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('2059', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "20",
CASE
WHEN (TO_CHAR(TO_DATE('2100', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('2100', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('2159', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('2100', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('2159', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "21",
CASE
WHEN (TO_CHAR(TO_DATE('2200', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('2200', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('2259', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('2200', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('2259', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "22",
CASE
WHEN (TO_CHAR(TO_DATE('2300', 'hh24mi'), 'hh24mi') BETWEEN
SSRMEET_BEGIN_TIME AND SSRMEET_END_TIME) THEN
'1'
WHEN (SSRMEET_BEGIN_TIME BETWEEN
TO_CHAR(TO_DATE('2300', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('2359', 'hh24mi'), 'hh24mi')) THEN
'1'
WHEN (SSRMEET_END_TIME BETWEEN
TO_CHAR(TO_DATE('2300', 'hh24mi'), 'hh24mi') AND
TO_CHAR(TO_DATE('2359', 'hh24mi'), 'hh24mi')) THEN
'1'
END AS "23",
NVL(SSRMEET_SUN_DAY, '') U,
NVL(SSRMEET_MON_DAY, '') M,
NVL(SSRMEET_TUE_DAY, '') T,
NVL(SSRMEET_WED_DAY, '') W,
NVL(SSRMEET_THU_DAY, '') R,
NVL(SSRMEET_FRI_DAY, '') F,
NVL(SSRMEET_SAT_DAY, '') S,
DAY_DESC GROUP_DAY_OF_WEEK,
SFR.SFRSTCR_PIDM PIDM
FROM SPRIDEN SPR_INSTRUCTOR,
SIRASGN SIR,
SSRMEET SSR,
(SELECT 'U' DAY_CODE, 'SUNDAY' DAY_DESC
FROM DUAL
UNION
SELECT 'M', 'MONDAY'
FROM DUAL
UNION
SELECT 'T', 'TUESDAY'
FROM DUAL
UNION
SELECT 'W', 'WEDNESDAY'
FROM DUAL
UNION
SELECT 'R', 'THURSDAY'
FROM DUAL
UNION
SELECT 'F', 'FRIDAY'
FROM DUAL
UNION
SELECT 'S', 'SATURDAY'
FROM DUAL) DAY_OF_WEEK,
SFRSTCR SFR,
SCBCRSE SCB,
SSBSECT SSB
WHERE SFR.SFRSTCR_PIDM IN
(SELECT S1.SGRSPRT_PIDM
FROM SGRSPRT S1, SGRATHC SGR
WHERE S1.SGRSPRT_TERM_CODE LIKE
'%' || SUBSTR(:parm_DD_TermCodes.CODE, 0, 4) || '%'
AND SGR.SGRATHC_PIDM(+) = S1.SGRSPRT_PIDM
AND SGR.SGRATHC_TERM_CODE(+) = S1.SGRSPRT_TERM_CODE
AND SGR.SGRATHC_ACTC_CODE(+) = S1.SGRSPRT_ACTC_CODE
AND S1.SGRSPRT_SPST_CODE = 'AC'
AND ((S1.SGRSPRT_ELIG_CODE = 'Y' AND S1.SGRSPRT_SAEL_CODE = 'EL' AND
EXISTS
(SELECT 'Y'
FROM SGRATHC SGR1
WHERE SGR1.SGRATHC_PIDM = SGR.SGRATHC_PIDM
AND SGR1.SGRATHC_SEASON_USED_IND = 'Y'
AND SGR1.SGRATHC_ACTIVITY_DATE =
(SELECT MAX(SGR2.SGRATHC_ACTIVITY_DATE)
FROM SGRATHC SGR2
WHERE SGR2.SGRATHC_PIDM = SGR1.SGRATHC_PIDM
AND SGR2.SGRATHC_TERM_CODE = SGR1.SGRATHC_TERM_CODE
AND SGR2.SGRATHC_ACTC_CODE = SGR1.SGRATHC_ACTC_CODE)
AND SGR1.SGRATHC_TERM_CODE IN
(SELECT T3.STVTERM_CODE
FROM STVTERM T3
WHERE T3.STVTERM_TRMT_CODE = 'S'
AND T3.STVTERM_CODE LIKE '%' || SUBSTR(:parm_DD_TermCodes.CODE, 0, 4) || '%'))))
AND S1.SGRSPRT_ACTC_CODE = :main_DD_Sport_Type.SPRT_CODE
AND S1.SGRSPRT_ACTIVITY_DATE =
(SELECT MAX(S4.SGRSPRT_ACTIVITY_DATE)
FROM SGRSPRT S4
WHERE S4.SGRSPRT_PIDM = S1.SGRSPRT_PIDM
AND S4.SGRSPRT_TERM_CODE = S1.SGRSPRT_TERM_CODE
AND S4.SGRSPRT_ACTC_CODE = S1.SGRSPRT_ACTC_CODE))
AND SFR.SFRSTCR_TERM_CODE = :parm_DD_TermCodes.CODE
AND SFR.SFRSTCR_CRN IN
(SELECT SFR1.SFRSTCR_CRN AS CRN
FROM STVTERM STV1, SFRSTCR SFR1
WHERE SFR1.SFRSTCR_PIDM = SFR.SFRSTCR_PIDM
AND STV1.STVTERM_CODE = :parm_DD_TermCodes.CODE
AND (SFR1.SFRSTCR_ERROR_FLAG != 'F' OR SFR1.SFRSTCR_ERROR_FLAG IS NULL)
AND SFR1.SFRSTCR_TERM_CODE = STV1.STVTERM_CODE
AND SFR1.SFRSTCR_RSTS_CODE NOT IN ('DD', 'WC'))
AND (SFR.SFRSTCR_ERROR_FLAG != 'F' OR SFR.SFRSTCR_ERROR_FLAG IS NULL)
AND SFR.SFRSTCR_RSTS_CODE NOT IN ('DD', 'WC')
AND SFR.SFRSTCR_TERM_CODE = SSR.SSRMEET_TERM_CODE
AND SFR.SFRSTCR_CRN = SSR.SSRMEET_CRN
AND DAY_OF_WEEK.DAY_CODE IN (SSR.SSRMEET_MON_DAY, SSR.SSRMEET_TUE_DAY, SSR.SSRMEET_WED_DAY,
SSR.SSRMEET_THU_DAY, SSR.SSRMEET_FRI_DAY)
AND SSR.SSRMEET_TERM_CODE = SIR.SIRASGN_TERM_CODE(+)
AND SSR.SSRMEET_CRN = SIR.SIRASGN_CRN(+)
AND SSR.SSRMEET_CATAGORY = SIR.SIRASGN_CATEGORY(+)
AND SIR.SIRASGN_PIDM = SPR_INSTRUCTOR.SPRIDEN_PIDM(+)
AND SPR_INSTRUCTOR.SPRIDEN_CHANGE_IND IS NULL
AND SFR.SFRSTCR_CRN = SSB.SSBSECT_CRN
AND SFR.SFRSTCR_TERM_CODE = SSB.SSBSECT_TERM_CODE
AND SSB.SSBSECT_SUBJ_CODE = SCB.SCBCRSE_SUBJ_CODE
AND SSB.SSBSECT_CRSE_NUMB = SCB.SCBCRSE_CRSE_NUMB
AND DAY_DESC = :chart_Avail_by_Day.Avail_by_Day.GROUP_DAY_OF_WEEK
AND (SCB.SCBCRSE_SUBJ_CODE || SCB.SCBCRSE_CRSE_NUMB) NOT LIKE 'ES0%' -- Exclude all "Sport" "classes" (eg. Football, Baseball, etc.)
AND SCB.SCBCRSE_EFF_TERM =
(SELECT MAX(SCB1.SCBCRSE_EFF_TERM)
FROM SCBCRSE SCB1
WHERE SCB1.SCBCRSE_SUBJ_CODE = SSB.SSBSECT_SUBJ_CODE
AND SCB1.SCBCRSE_CRSE_NUMB = SSB.SSBSECT_CRSE_NUMB
AND SCB1.SCBCRSE_EFF_TERM <= SFR.SFRSTCR_TERM_CODE)) ATHL_CRSE_CONF
GROUP BY ATHL_CRSE_CONF.GROUP_DAY_OF_WEEK)
SELECT HOUR_CODE, HOUR_COUNTS
FROM TEMP_ATH_SCHEDULE
UNPIVOT(
HOUR_COUNTS
FOR HOUR_CODE IN (Hour00,Hour01,Hour02,Hour03,Hour04,Hour05,Hour06,Hour07,Hour08,Hour09,Hour10,Hour11,Hour12,Hour13,Hour14,Hour15,Hour16,Hour17,Hour18,Hour19,Hour20,Hour21,Hour22,Hour23)
)
ORDER BY HOUR_CODE ASC
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论