+ Post New Thread
Results 1 to 4 of 4
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, ...
  1. #1

    Join Date
    Feb 2012
    Posts
    5
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    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.

    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.

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,528
    Thank Post
    1,460
    Thanked 1,214 Times in 823 Posts
    Rep Power
    724
    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 10:39 AM. Reason: Grammar.

  3. #3

    Join Date
    Feb 2012
    Posts
    5
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Nice one. Thank you.

  4. #4

    Join Date
    Jun 2012
    Location
    United Kingdom
    Posts
    3
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    I actually think is nicer than CMIS' ...

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] Urgent Help needed !! Can't logon to sims.net
    By artfulmatt in forum MIS Systems
    Replies: 13
    Last Post: 2nd December 2010, 11:02 PM
  2. CMIS to SIMS
    By SpuffMonkey in forum MIS Systems
    Replies: 10
    Last Post: 11th November 2008, 03:21 PM
  3. New to Sims - help on permissions.
    By Kyle in forum MIS Systems
    Replies: 5
    Last Post: 1st July 2008, 10:20 AM
  4. Replies: 12
    Last Post: 22nd October 2007, 07:15 PM
  5. SIMS SQL upgrade. To Express or not to Express?
    By woody in forum MIS Systems
    Replies: 5
    Last Post: 16th October 2007, 04:34 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •