Help turning a CMIS SQL query to SIMS

[trunten]

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.

 

SQL:

 

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]

 

Headings:

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.

 

 

Any help would be greatly appreciated.

[LosOjos]

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.

 

Your best bet is going to be to create some reports containing this data in SIMS, then programatically extract these reports using Command Reporter - this thread should get you started, and there are lots of threads on the subject around here: http://www.edugeek.net/forums/mis-systems/83551-accessing-sims-database-querying-retrieve-information.html

Edited May 30, 2012 by LosOjos
Grammar.

[trunten]

Nice one. Thank you.

[Hoce]

I actually think is nicer than CMIS' ...