+ Post New Thread
Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
MIS Systems Thread, Table Details for Serco Facility Admin in Technical; The values separating the results relate to that criterias map value. This can be seen in the Assessment templates section ...
  1. #16
    JPS
    JPS is offline
    JPS's Avatar
    Join Date
    May 2010
    Location
    Chelmsford
    Posts
    293
    Thank Post
    91
    Thanked 44 Times in 40 Posts
    Rep Power
    12

    Criteriadata

    The values separating the results relate to that criterias map value. This can be seen in the Assessment templates section of the Assessment module.

    You may want to then refer to the assesscriteria table

    Hope this helps.
    Attached Images Attached Images

  2. Thanks to JPS from:

    atish (10th June 2010)

  3. #17
    SkywOrca's Avatar
    Join Date
    Sep 2008
    Posts
    88
    Thank Post
    1
    Thanked 18 Times in 17 Posts
    Rep Power
    48
    NSTURESULTS is extremely difficult to manipulate. As you've found, the criteria data itself is compiled into a single string using a separator (which is actually the Line Feed character, Char 10 on the ASCII table). But worse still is that once the length of the string exceeds 40 characters it splits the additional content into a new row of data. All of which basically means that it's not an efficient table to extract data from.

    The only way you can access the data through pure SQL is to write a cursor driven stored function. SQL is fundamentally a declarative language (that is it doesn't operate on flow control, i.e. if statements etc, it works by declaring links between things), so writing cursors is automatically an inefficient use of the underlying system. What this basically means is that this code will be sloooow...

    However, that said, I did write come SQL that will extract the contents of this table into a slightly more user-friendly (and SQL friendly) collection of CriterId : CriterValue pairs along with associated key fields (SetId, StudentId etc...).
    Code:
    -- Create the Criteria table function:
    
    CREATE FUNCTION udfSTUCRITERIA (@strSetId VARCHAR(10), @strStudentId VARCHAR(10), @intRecordNum INT)
    RETURNS @retSTUCRITERIA TABLE (SetId VARCHAR(10) COLLATE database_default, StudentId VARCHAR(10) COLLATE database_default, RecordNum INT, CriterId VARCHAR(10) COLLATE database_default, CriterValue VARCHAR(8000) COLLATE database_default) AS
    BEGIN
    	DECLARE @strCriteriaData VARCHAR(40), @strFullCrit VARCHAR(8000), @strCriterId VARCHAR(10), @strCriterValue VARCHAR(8000), @strCriterBit VARCHAR(1)
    	DECLARE @intNumRecords INT
    	SELECT @strFullCrit = '', @strCriterId = '', @strCriterValue = '', @strCriterBit = 'I'
    	SELECT @intNumRecords = COUNT(*) FROM NSTURESULTS WHERE SetId = @strSetId AND StudentId = @strStudentId AND RecordNum = @intRecordNum
    	IF @intNumRecords > 1
    	BEGIN
    		DECLARE curNSTURESULTS CURSOR FOR SELECT CriteriaData FROM NSTURESULTS WHERE SetId = @strSetId AND StudentId = @strStudentId AND RecordNum = @intRecordNum ORDER BY LineNum
    		OPEN curNSTURESULTS
    		FETCH NEXT FROM curNSTURESULTS INTO @strCriteriaData
    		WHILE @@FETCH_STATUS = 0
    		BEGIN
    			SELECT @strFullCrit = @strFullCrit + @strCriteriaData
    			FETCH NEXT FROM curNSTURESULTS INTO @strCriteriaData
    		END
    		CLOSE curNSTURESULTS
    		DEALLOCATE curNSTURESULTS
    	END
    	ELSE
    		SELECT @strFullCrit = CriteriaData FROM NSTURESULTS WHERE SetId = @strSetId AND StudentId = @strStudentId AND RecordNum = @intRecordNum
    	WHILE DATALENGTH(@strFullCrit) > 0
    	BEGIN
    		IF @strCriterBit = 'I'
    		BEGIN
    			IF LEFT(@strFullCrit, 1) = CHAR(10)
    				SELECT @strCriterBit = 'V'
    			ELSE
    				SELECT @strCriterId = @strCriterId + LEFT(@strFullCrit, 1)
    		END
    		ELSE
    		BEGIN
    			IF LEFT(@strFullCrit, 1) = CHAR(10)
    			BEGIN
    				INSERT INTO @retSTUCRITERIA SELECT @strSetId, @strStudentId, @intRecordNum, @strCriterId, @strCriterValue
    				SELECT @strCriterBit = 'I', @strCriterId = '', @strCriterValue = ''
    			END
    			ELSE
    				SELECT @strCriterValue = @strCriterValue + LEFT(@strFullCrit, 1)
    		END
    		SELECT @strFullCrit = RIGHT(@strFullCrit, DATALENGTH(@strFullCrit) - 1)
    	END
    	RETURN
    END
    
    -- Create the Criteria View:
    
    CREATE VIEW vNSTURESCRIT AS
    SELECT DISTINCT s.SetId, s.StudentId, s.RecordNum, u.CriterId, u.CriterValue FROM NSTURESULTS s CROSS APPLY udfSTUCRITERIA(s.SetId, s.StudentId, s.RecordNum) u
    This should result in a view (vNSTURESCRIT) that you can incorporate into your SQL statements to analyse the data further. I would strongly recommend that you actually feed the results from the view into a table somewhere on your system if you're going to be pulling lots of data out at a time in order to minimise the computational impact of the cursors.

    As always, SQL should be used with care, make sure you have backups etc just in case even though this script does nothing to write data back - you never can be too careful IMO. The script also comes with no warranty that it'll work without modification on your system, or that it won't corrupt your data, cause your server to catch fire, or cause the Moon to spin off into outer space, but none of the above should happen.

  4. Thanks to SkywOrca from:

    atish (10th June 2010)

  5. #18


    Join Date
    May 2009
    Posts
    3,118
    Thank Post
    273
    Thanked 836 Times in 628 Posts
    Rep Power
    332
    A useful technique when trying to find out where data is stored is to snapshot a count of all the records in the database tables, add something to the area you are interested in, snapshot again and then look at which tables have changed. I have a nice little spreadsheet which does this - I'll have a look at making it available somewhere.

    For results I'd agree with SkywOrca - difficult to manipulate. We deal with them via a stored proc to extract the result attribute we're after (so you need to know the mapvalue of the attribute) and a view which combines multiple line criteria data (where they go over 40 chars) into a single field. SQL for the view is :

    Code:
    select case when nsr1.criteriadata is null then '' else nsr1.criteriadata end + 
           case when nsr2.criteriadata is null then '' else nsr2.criteriadata end +
           case when nsr3.criteriadata is null then '' else nsr3.criteriadata end "AllCriteria",
           nsr1.*
    
      from nsturesults nsr1 left join nsturesults nsr2 on nsr1.studentid = nsr2.studentid and
                                                          nsr1.recordnum = nsr2.recordnum and 
                                                          nsr1.linenum + 1 = nsr2.linenum                                                   
                            left join nsturesults nsr3 on nsr1.studentid = nsr3.studentid and
                                                          nsr1.recordnum = nsr3.recordnum and 
                                                          nsr1.linenum + 2 = nsr3.linenum
     where nsr1.linenum = 0
    The SQL will only deal with criteria split over 3 records which is the most we currently have. The stored proc for extracting the result attribute is :

    Code:
    CREATE FUNCTION ResFromCrit( @critdata varchar(200), @MapValue varchar(10) ) returns varchar(20) AS
    BEGIN
    
    
    	Declare @StartPos as int			-- Start position in critdata of the mapvalue
    	Declare @RPart as varchar(200)		-- Part of the string to the right of MapValue
    	Declare @RPPos as int				-- Position of next space after map attribute
    	Declare @FReturn as varchar(20)
    	Declare @DSLen as int
    	Declare @DSStr as varchar(20)
    	Declare @LPPos as int
    	Declare @LPart as varchar(200)
    
    	Declare @Result as varchar(50)	-- Final function return value
    
      
    	SET @Result = ''
    
    	-- Get the position of the beginning of the first instance (if there is one)
    	SET @StartPos = charindex(@MapValue+char(10),@critdata) 
    
       -- If the particular mapvalue is not present anywhere, then startpos will be zero 
    	IF (@StartPos = 0)
    		SET @Result = NULL
    	ELSE BEGIN
    	
       	-- Get the string part which starts after the Mapvalue and the chr(10) delimiter
       	SET @RPart = right( @critdata, len(@critdata)-(@startpos+len(@mapvalue)) )
       	
       	-- Find the next chr(10) (delimits the end of the attribute of the mapvalue)
       	SET @RPPos = charindex(char(10),@RPart)
       	
       	-- Length of the data is one less than the position of the space (if found)
       	SET @DSLen = @RPPos - 1 
       	
       	-- Reality check on string length - sometimes there is no attribute, in which case
       	-- the length is zero
       	IF (@DSLen > 0) BEGIN
      	   	
    			-- We need to check that there is a delimiter infront of the MapValue or that
    			-- it was at the start of the string. Otherwise it's an embedded number i.e.
    			-- looking for Mapvalue = 179 and finding it in the number 34179
    			IF (@StartPos = 1)
    				SET @Result = 'AT START' 
    			ELSE
    			
    				SET @LPart = left (@critdata, (@StartPos)-1)
    				
    				IF (right(@LPart,1)<>char(10))
    				   SET @Result = NULL
    				ELSE BEGIN
    					SET @Result = LEFT( @RPart, @RPPos -1 )
    				END
    			
    		END ELSE
    			SET @Result = NULL
      	END
    
      	RETURN @Result
      
    END
    This can then be used in SQL, i.e we want a single record returned for students in year 7 with their results listed as columns :

    Code:
    select nstupersonal.studentid,   
           nstupersonal.forename,    
           nstupersonal.surname,      
     max( case when PCS_StuCritData.moduleid = 'AR' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "AR",
     max( case when PCS_StuCritData.moduleid = 'DA' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "DA",
     max( case when PCS_StuCritData.moduleid = 'DR' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "DR",
     max( case when PCS_StuCritData.moduleid = 'DT' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "DT",
     max( case when PCS_StuCritData.moduleid = 'EH' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "EH",
     max( case when PCS_StuCritData.moduleid = 'FR' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "FR",
     max( case when PCS_StuCritData.moduleid = 'IT' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "IT",
     max( case when PCS_StuCritData.moduleid = 'MA' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "MA",
     max( case when PCS_StuCritData.moduleid = 'MU' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "MU",
     max( case when PCS_StuCritData.moduleid = 'PE' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "PE",
     max( case when PCS_StuCritData.moduleid = 'PSHE' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "PSHE",
     max( case when PCS_StuCritData.moduleid = 'SC' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "SC"
     from nstupersonal left join PCS_StuCritData on nstupersonal.studentid = PCS_StuCritData.studentid, 
          students  where nstupersonal.studentid = students.studentid AND 
           nstupersonal.setid = '2009/2010' AND 
           students.setid = '2009/2010' AND 
           NSTUPERSONAL.DateLeft = '' AND 
           NSTUPERSONAL.LeftSchool = 'N' AND 
           students.courseyear = '7' AND 
           ( PCS_StuCritData.assessid = 'KS3Assess' OR PCS_StuCritData.assessid IS NULL ) AND 
           ( PCS_StuCritData.examid = 'Year7' OR PCS_StuCritData.examid IS NULL ) 
    Group by nstupersonal.studentid, nstupersonal.forename, nstupersonal.surname 
    Order by nstupersonal.studentid
    Where PCS_StuCritData is the view created by the first bit of code.
    Last edited by pcstru; 9th June 2010 at 03:23 PM.

  6. Thanks to pcstru from:

    atish (10th June 2010)

  7. #19


    Join Date
    May 2009
    Posts
    3,118
    Thank Post
    273
    Thanked 836 Times in 628 Posts
    Rep Power
    332
    Quote Originally Posted by pcstru View Post
    A useful technique when trying to find out where data is stored is to snapshot a count of all the records in the database tables, add something to the area you are interested in, snapshot again and then look at which tables have changed. I have a nice little spreadsheet which does this - I'll have a look at making it available somewhere.
    Spreadsheet should be attached, brief instructions are included.
    Attached Files Attached Files

  8. Thanks to pcstru from:

    atish (10th June 2010)

  9. #20
    Marci's Avatar
    Join Date
    Jun 2008
    Location
    Wakefield, West Yorkshire
    Posts
    895
    Thank Post
    84
    Thanked 235 Times in 194 Posts
    Rep Power
    82
    do you have the link for this module?

    is it the one from the ossett school?

    would you be able to screenshot some of your screens please?
    A bit late, but what you're after is the MIS Block that got removed from Guy's Facility-to-Moodle project. It's this part that gives access to attendance, timetable, behaviour etc etc.
    It's still available separately, but appears not to be listed in Moodle Modules area. Gimme a shout if you need a copy.
    Last edited by Marci; 29th September 2010 at 02:19 AM.

  10. #21
    jonawd's Avatar
    Join Date
    Dec 2009
    Location
    Hayfield, High Peak
    Posts
    45
    Thank Post
    3
    Thanked 7 Times in 7 Posts
    Rep Power
    11
    Marci,

    Yes please!

  11. #22
    Marci's Avatar
    Join Date
    Jun 2008
    Location
    Wakefield, West Yorkshire
    Posts
    895
    Thank Post
    84
    Thanked 235 Times in 194 Posts
    Rep Power
    82
    ygpm fella...

  12. #23

    Join Date
    May 2010
    Posts
    4
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Hi Marci -

    Quote Originally Posted by Marci View Post
    A bit late, but what you're after is the MIS Block that got removed from Guy's Facility-to-Moodle project. It's this part that gives access to attendance, timetable, behaviour etc etc.
    It's still available separately, but appears not to be listed in Moodle Modules area. Gimme a shout if you need a copy.
    can I have a copy of that as well pls.

  13. #24

    SpuffMonkey's Avatar
    Join Date
    Jul 2005
    Posts
    2,240
    Thank Post
    55
    Thanked 278 Times in 186 Posts
    Rep Power
    134
    And me!

SHARE:
+ Post New Thread
Page 2 of 2 FirstFirst 12

Similar Threads

  1. [ACS] Serco's Facility
    By ANiceEnglishman in forum MIS Systems
    Replies: 49
    Last Post: 3rd April 2011, 01:42 PM
  2. serco facility
    By garrysaddington in forum MIS Systems
    Replies: 8
    Last Post: 8th June 2009, 10:22 AM
  3. Serco Facility Experiences!?
    By Nick_Young in forum MIS Systems
    Replies: 18
    Last Post: 6th March 2009, 03:10 PM
  4. Fronter and Serco Facility
    By cheredenine in forum Virtual Learning Platforms
    Replies: 3
    Last Post: 10th November 2008, 08:24 AM
  5. Serco CMIS Facility
    By bursar in forum MIS Systems
    Replies: 11
    Last Post: 22nd May 2007, 04:36 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
  •