Attendance Record SQL Query Statistics (Continuously Updated)

Developers
1

Table: gibbonattendancecode
Purpose: Records the attendance code and its corresponding descriptive name (text format).

  • gibbonAttendanceCodeID: Attendance Code ID
  • nameShort: Attendance Name
  • type: Attendance Type
  • direction: Indicates whether the attendance is on campus
  • active: Indicates whether the attendance code is active

Table: gibbonattendancelogperson
Purpose: Stores each student’s attendance record.

  • gibbonAttendanceLogPersonID: Attendance Record Serial Number
  • gibbonAttendanceCodeID: References gibbonattendancecode.gibbonAttendanceCodeID
  • gibbonPersonID: References gibbonperson.gibbonPersonID (person information table)

Note: The student’s name can be obtained via gibbonperson.surname

  • type: Attendance Type
  • reason: Reason for attendance (not used in this application)
  • date: Attendance Date
  • gibbonPersonIDTaker: Attendance Teacher, references gibbonperson.gibbonPersonID (person information table)
  • gibbonCourseClassID: Attendance Course ID
  • gibbonTTDayRowClassID: Course Schedule, references gibbonttdayrowclass.gibbonTTDayRowClassID
  • timestampTaken: Attendance Timestamp (records the exact time the attendance was taken)

Table: gibboncourse
Purpose: Records course information.

  • gibbonCourseID: Course ID, referenced by gibbonattendancelogperson.gibbonCourseClassID
  • gibbonSchoolYearID: Course School Year ID (not used in this application)
  • gibbonDepartmentID: Learning Area ID
  • nameShort: Course Name

Table: gibboncourseclass
Purpose: Records course group information (treated as an attendance unit, similar to a class).

  • gibbonCourseClassID: Course Class ID, referenced by gibbonattendancelogperson.gibbonCourseClassID
  • gibbonCourseID: Course ID, references gibboncourse.gibbonCourseID
  • nameShort: Class Name; should be concatenated as gibboncourse.nameShort + gibboncourseclass.nameShort

Note: The concatenation of gibboncourse.nameShort + gibboncourseclass.nameShort represents a specific class for a subject; a student may be assigned to multiple classes for a course.

  • attendance: Indicates whether attendance is required; courses generally marked as “N” will not have attendance records, so this field can usually be ignored

Table: gibboncourseclassperson
Purpose: Records registration information for each course class.

  • gibbonCourseClassPersonID: Registration Entry ID
  • gibbonCourseClassID: Course Class ID, references gibboncourseclass.gibbonCourseClassID
  • gibbonPersonID: References gibbonperson.gibbonPersonID (person information table)
  • role: Role (usually only “Student” requires attendance)
  • dateEnrolled: Date the person was assigned to the class; attendance records are typically recorded only after this date

Table: gibbonperson
Purpose: Stores all personnel information including names and identity details.

  • gibbonPersonID: Unique User ID, referenced by various tables
  • surname: In this application, stores the full name rather than just the surname
  • officialName: Essentially the same as surname; use surname whenever possible
  • nameInCharacters: User’s English Name
  • gender: User’s Gender (not always accurate and generally not used)
  • username: User Login Username
  • gibbonRoleIDPrimary: User Role ID, references gibbonrole.gibbonRoleID

Table: gibbonrole
Purpose: Records the identity information for personnel, primarily used for access control; for attendance, it mainly denotes student identity.

  • gibbonRoleID: User Role ID (currently, only users with ID “003” are considered students requiring attendance)
  • category: Role Category, typically indicating whether the role is for a teacher, student, or parent

Table: gibbonschoolyear
Purpose: Records school year information.

  • gibbonSchoolYearID: School Year ID
  • name: School Year Name (e.g., “2024 School Year”, “2025 School Year”)
  • status: Status (only one “Current” represents the current school year)
  • firstDay: Start Date
  • lastDay: End Date

Table: gibbonyeargroup
Purpose: Records grade level information.

  • gibbonYearGroupID: Grade Level ID
  • name: Grade Name (e.g., Grade One, Grade Two, Grade Three)

Table: gibbontt
Purpose: Records the timetable name; each grade can only have one timetable.

  • gibbonTTID: Timetable ID
  • gibbonSchoolYearID: School Year ID, references gibbonschoolyear.gibbonSchoolYearID
  • name: Timetable Name
  • nameShort: Timetable Abbreviated Name
  • gibbonYearGroupIDList: Grade Level ID, references gibbonyeargroup
  • active: Indicates whether the timetable is active (Y = active, N = inactive)

Table: gibbonttday
Purpose: Records the daily timetable schedule.

  • gibbonTTDayID: Timetable Day ID
  • gibbonTTID: Timetable ID, references gibbontt.gibbonTTID
  • gibbonTTColumnID: Schedule Column ID (associated with a timetable column)
  • name: Timetable Name; typically, one timetable corresponds to a specific day of the week, indicating the start and end times. For a day with N periods, there will be N start times and N end times

Table: gibbonttdaydate
Purpose: Records the natural date corresponding to the timetable information.

  • gibbonTTDayDateID: Day Date ID
  • gibbonTTDayID: Timetable Day ID, references gibbonttday.gibbonTTDayID (indicates which timetable is used on that day)
  • date: Calendar Date

Table: gibbonttcolumn
Purpose: Records timetable column names used for storing scheduling information.

  • gibbonTTColumnID: Timetable Column ID
  • name: Timetable Column Name (e.g., indicates grade level and day of the week; note that elementary schools may not have classes on Friday while high schools might have an extra evening study session on Monday, hence the differentiation)

Table: gibbonttcolumnrow
Purpose: Records the scheduling details for each timetable row.

  • gibbonTTColumnRowID: Timetable Row Scheduling ID
  • gibbonTTColumnID: References gibbonttcolumn.gibbonTTColumnID
  • name: Scheduling Information Name (e.g., period number, lunch break, evening study, etc.)
  • timeStart: Start Time
  • timeEnd: End Time

Table: gibbonttdayrowclass
Purpose: Records the course information corresponding to a timetable slot, i.e., which classes are scheduled during that time period. For example, on Monday, during Grade Three’s first period from 10 to 11, assume three classes are in session.

  • gibbonTTDayRowClassID: Timetable Row Class Scheduling ID
  • gibbonTTColumnRowID: Timetable Row Scheduling ID, references gibbonttcolumnrow.gibbonTTColumnRowID
  • gibbonTTDayID: Timetable Day ID
  • gibbonCourseClassID: Course Class ID
  • gibbonSpaceID: Classroom ID (generally, a classroom can only host one class during a given time slot)

Table: gibbonspace
Purpose: Records classroom information.

  • gibbonSpaceID: Classroom ID
  • name: Classroom Name
  • type: Classroom Type
2

SQL Query Powered by ChatGPT…

Query Person By Date

SET @targetDate = '2025-02-24';
SET @personID   = '0000000050';

WITH schedule AS (
  -- 查询该学生在指定日期的所有排课记录及相关信息
  SELECT 
    cc.gibbonCourseClassID,
    p.gibbonPersonID,
    td.date AS course_date,
    trc.gibbonTTDayRowClassID,
    colrow.name AS periodName,
    colrow.timeStart,
    CONCAT(c.nameShort, '-', cc.nameShort) AS mergedCourseName
  FROM gibboncourseclassperson AS p
  JOIN gibboncourseclass AS cc 
    ON p.gibbonCourseClassID = cc.gibbonCourseClassID
  JOIN gibboncourse AS c 
    ON cc.gibbonCourseID = c.gibbonCourseID
  JOIN gibbonttdayrowclass AS trc 
    ON cc.gibbonCourseClassID = trc.gibbonCourseClassID
  JOIN gibbonttdaydate AS td 
    ON trc.gibbonTTDayID = td.gibbonTTDayID
  JOIN gibbonttcolumnrow AS colrow 
    ON trc.gibbonTTColumnRowID = colrow.gibbonTTColumnRowID
  JOIN gibbonperson AS per 
    ON p.gibbonPersonID = per.gibbonPersonID
  WHERE p.gibbonPersonID = @personID
    AND per.gibbonRoleIDPrimary = '003'
    AND td.date = @targetDate
),
latest_attendance AS (
  -- 对于指定学生和日期，每个 (gibbonTTDayRowClassID, gibbonCourseClassID, gibbonPersonID, date) 分组，取最新的一条考勤记录
  SELECT 
      gibbonTTDayRowClassID,
      gibbonCourseClassID,
      gibbonPersonID,
      date,
      type,
      timestampTaken,
      ROW_NUMBER() OVER (
         PARTITION BY gibbonTTDayRowClassID, gibbonCourseClassID, gibbonPersonID, date 
         ORDER BY timestampTaken DESC
      ) AS rn
  FROM gibbonattendancelogperson
  WHERE gibbonPersonID = @personID
    AND date = @targetDate
)
SELECT 
    s.mergedCourseName,
    s.gibbonCourseClassID,
    s.gibbonPersonID,
    s.periodName,
    s.timeStart,
    s.course_date,
    -- 如果对应排课记录有考勤，则取最新的记录；如果没有，则显示“无记录”
    COALESCE(a.type, '无记录') AS attendanceType,
    a.timestampTaken AS latestTimestamp
FROM schedule AS s
LEFT JOIN latest_attendance AS a
  ON a.gibbonTTDayRowClassID = s.gibbonTTDayRowClassID
  AND a.gibbonCourseClassID = s.gibbonCourseClassID
  AND a.gibbonPersonID = s.gibbonPersonID
  AND a.date = s.course_date
  AND a.rn = 1
ORDER BY s.timeStart, s.gibbonCourseClassID, s.gibbonPersonID;

Result (I apologize, but my Firefox browser doesn’t support translation.)

iShot_2025-02-25_22.06.51
iShot_2025-02-25_22.06.512920×1972 935 KB

3

SQL Query Powered by ChatGPT…

Query Lesson By Date

SET @targetDate = '2025-02-24';
SET @courseClassID = '00000197';

WITH latest_attendance AS (
  SELECT 
    gibbonTTDayRowClassID,
    gibbonCourseClassID,
    gibbonPersonID,
    date,
    type,
    timestampTaken,
    ROW_NUMBER() OVER (
       PARTITION BY gibbonTTDayRowClassID, gibbonCourseClassID, gibbonPersonID, date 
       ORDER BY timestampTaken DESC
    ) AS rn
  FROM gibbonattendancelogperson
  WHERE date = @targetDate
),
base AS (
  SELECT 
    c.gibbonCourseID,
    CONCAT(c.nameShort, '-', cc.nameShort) AS mergedCourseName,
    cc.gibbonCourseClassID,
    p.gibbonPersonID,
    per.surname,
    per.officialName,
    colrow.name AS periodName,
    colrow.timeStart,
    td.date AS course_date,
    att.timestampTaken,
    COALESCE(att.type, '无记录') AS raw_attendanceType
  FROM gibboncourseclass AS cc
  JOIN gibboncourse AS c 
    ON cc.gibbonCourseID = c.gibbonCourseID
  JOIN gibbonttdayrowclass AS trc 
    ON cc.gibbonCourseClassID = trc.gibbonCourseClassID
  JOIN gibbonttdaydate AS td 
    ON trc.gibbonTTDayID = td.gibbonTTDayID
  JOIN gibbonttcolumnrow AS colrow 
    ON trc.gibbonTTColumnRowID = colrow.gibbonTTColumnRowID
  JOIN gibboncourseclassperson AS p 
    ON cc.gibbonCourseClassID = p.gibbonCourseClassID
  JOIN gibbonperson AS per 
    ON p.gibbonPersonID = per.gibbonPersonID
  LEFT JOIN latest_attendance AS att 
    ON att.gibbonTTDayRowClassID = trc.gibbonTTDayRowClassID
   AND att.gibbonCourseClassID = cc.gibbonCourseClassID
   AND att.gibbonPersonID = p.gibbonPersonID
   AND att.date = td.date
   AND att.rn = 1
  WHERE per.gibbonRoleIDPrimary = '003'
    AND cc.gibbonCourseClassID = @courseClassID
    AND td.date = @targetDate
)
SELECT 
  gibbonCourseID,
  mergedCourseName,
  gibbonCourseClassID,
  gibbonPersonID,
  surname,
  officialName,
  periodName,
  timeStart,
  course_date,
  timestampTaken,
  CASE 
    WHEN timeStart = MIN(timeStart) OVER (
         PARTITION BY mergedCourseName, gibbonCourseClassID, gibbonPersonID, course_date
       )
    THEN raw_attendanceType
    ELSE '无记录'
  END AS attendanceType
FROM base
ORDER BY mergedCourseName, timeStart, gibbonPersonID;

Result (I apologize, but my Firefox browser doesn’t support translation.)

iShot_2025-02-25_22.24.21
iShot_2025-02-25_22.24.212196×1258 470 KB