MIS Systems Thread, Help turning a CMIS SQL query to SIMS in Technical; I currently have a SQL query that I use to create a spreadsheet for use in another program I use, ...
30th May 2012, 09:47 AM #1
- Rep Power
Help turning a CMIS SQL query to SIMS
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.
Any help would be greatly appreciated.
IDG Tech News
30th May 2012, 10:22 AM #2
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: Accessing the SIMS database and querying it to retrieve information
Last edited by LosOjos; 30th May 2012 at 11:39 AM.
30th May 2012, 11:36 AM #3
- Rep Power
11th June 2012, 05:06 PM #4
- Rep Power
I actually think is nicer than CMIS' ...
By artfulmatt in forum MIS Systems
Last Post: 3rd December 2010, 12:02 AM
By SpuffMonkey in forum MIS Systems
Last Post: 11th November 2008, 04:21 PM
By Kyle in forum MIS Systems
Last Post: 1st July 2008, 11:20 AM
By Grommit in forum Windows
Last Post: 22nd October 2007, 08:15 PM
By woody in forum MIS Systems
Last Post: 16th October 2007, 05:34 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)