I currently have a SQL query that I use to create a spreadsheet for use in another program I use, however, I need to some how make an identical result set but using SIMS instead of CMIS. If there is anybody out there with knowledge of both that could take a look at my query and let me know if it is transposable to SIMS and also how to do that.
select lecturer.displectid as [Staff_ID], 'TT_Time' = (Case a.weekday when '1' then 'Monday' when '2' then 'Tuesday' when '3' then 'Wednesday' when '4' then 'Thursday' when '5' then 'Friday' else '' end + case left(a.starttime,2) when '08' then 'REG' when '09' then '1' when '10' then '2' when '11' then '3' when '13' then '4' when '14' then '5' else '' end), a.moduleid as [TT_Subject], a.roomid as [TT_Room], f.GroupCode as [TT_Group], d.StudentId as [ID], d.Surname, d.Forename, c.classgroupid as [Class], d.stusex as [Gender], e.slascgiftedtalented as [G&T], 'N' as [SEN], e.fsmeligible as [FSME] from lecturer right join (timetable a left join (teachinggroups f inner join (stugroups b inner join (students c inner join (nstupersonal d inner join ukstustats e on d.studentid = e.studentid and d.setid = e.setid) on c.studentid = d.studentid and c.setid = d.setid) on b.studentid = c.studentid and b.setid = c.setid) on b.groupid = f.groupid and f.setid = b.setid) on a.groupid = b.groupid and a.setid = b.setid) on a.lecturerid = lecturer.lecturerid and a.setid = lecturer.setid where a.setid = '2011/2012' and (d.leftschool = 'n' or a.moduleid != '') order by lecturer.displectid, [TT_Time]
Rec num - Record number
Staff_ID - CMIS Staff ID
TT_Time - Time of the period
TT_Subject - Subject of the timetable period
TT_Room - Room of the timetable period
TT_Group - Group code of the timetable period
ID - Student ID
Surname - Student Surname
Forename - Student Forename
Class - Student Class/Form group
Gender - Student Gender
G&T - Is the student G&T? (Y/N)
SEN - Student's SEN category (A,P,S,N)
FSME - Is the student FSM? (Y/N)
For each timetbale period I am pulling through the staff and pupils associated so there are multiple rows (1 for each pupil / staff member) for each timetable event.
SIMS' SQL database is horribly complex and has a habit of changing with upgrades; Capita strongly discourage direct SQL access (you won't receive any support if you mess something up while directly accessing the database) and this may well be why the database is such a labyrinth.