Serco CMIS - Attendance tables?

Hi all,

Would anyone know which tables in Cerco CMIS contain the following data sets...

Personal Details
Attendance Info
Timetable
Assessment Results

Having a bit of trouble finding the correct tables for this info, any help would be appreciated!

The infamous NSTUResults is Results, NSTUPERSONAL is Personal Details as for the other ones they escape my mind as I haven't needed to poke them but its probably something similar. Anything NSTU is related to the student and they are actually, despite what people say, fairly logically labelled if you just look in SQL Manager at the actual tables.

As John pointed out, NSTURESULTS holds assessments data, but you might also need to make reference to ASSESSCRITERIA to see if it's a comment type, and if it is you'll need to cross refer to STURESCOMM to get the actual comment data out rather than the numeric CommentId.

Timetable data is kind of spread out across several tables, it really depends on what you need to get at the SQL of this data for. The main bulk of it is in TIMETABLE, but you'll find that much of this is encoded as object Ids (like SlotId, WeekId etc.), and you'll need to find the related tables to bring out the human-readable version of this info.

Attendance is split across two tables, ATTDETAIL and NSTUATTDATA. The data in each should be identical, it's horses for courses as to which you prefer, personally I find the data format in ATTDETAIL to be easier to work with.

Personal data is stored mainly in STUDENTS, NSTUPERSONAL and UKSTUSTATS. Still depends on exactly what you're after, however.

Excellent, thanks alot guys, should help out with what I'm planning.

Thank you Skyw0rca for the comments below.

I'd like to pick your brains a little more if I can. Looking at these tables reveals AM and PM Roll CAll attendance data for a student in two different formats as you have said, but it appears only to show the exceptions where a student is late, absent or other. Am I misreading the table data of is there another table for LessonByLesson attendance for every occurance of an attendance submission through ePortal?

Thanks Martin

Quote:

---

Originally Posted by SkywOrca

As John pointed out, NSTURESULTS holds assessments data, but you might also need to make reference to ASSESSCRITERIA to see if it's a comment type, and if it is you'll need to cross refer to STURESCOMM to get the actual comment data out rather than the numeric CommentId.

Timetable data is kind of spread out across several tables, it really depends on what you need to get at the SQL of this data for. The main bulk of it is in TIMETABLE, but you'll find that much of this is encoded as object Ids (like SlotId, WeekId etc.), and you'll need to find the related tables to bring out the human-readable version of this info.

Attendance is split across two tables, ATTDETAIL and NSTUATTDATA. The data in each should be identical, it's horses for courses as to which you prefer, personally I find the data format in ATTDETAIL to be easier to work with.

Personal data is stored mainly in STUDENTS, NSTUPERSONAL and UKSTUSTATS. Still depends on exactly what you're after, however.

---

Sorry, I didn't point out that these tables only relate to Roll Call attendance (the AM and PM attendances). Lesson-by-lesson is indeed in another table, the perhaps even more incomprehensible TTATTSTUDENTS table.

This one lists the SlotId (from TIMETABLE, which you'll probably need to link to several other tables to make sense of) relating to the lesson, the student id and a collection of Attr1, Attr2, ..., Attr40 columns which then map to the week number for the actual lesson on a particular day. You'd need to map these week numbers via CCALMAPS and the WeekDay of the slot to identify the actual date that these attributes map back to. It's possible I've skipped a step in all this since I'm pretty sure you also need to map this data via the WeekId to finally link to the specific date required...

Did I mention how difficult this table is? :confused: