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
1 Like
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

4

Hello, would it possible to tell me where you are getting the names of the fields? We are trying to write some custom SQL scripts for our school but unfortunately, we cannot find any good documentation for this.

5

Hi @nathan

Maybe you could you try using phpMyadmin. It should allow you to access the tables and relevant fields in your database. Thanks!

6

Hi, I did try this and I did get access to the table fields! Thank you!

1 Like