SharePoint Learning Kit Session Time

Topics: Developer Forum, Project Management Forum, User Forum
Jun 19, 2012 at 1:05 PM

Someone at the company I work for would like to be able to see how long an employee is spending on different training modules that we are trying to implement through SLK. I've seen things with SCORM in that it contains a session time value which sounds like what we want. First off, is it possible to have this value store as a column in Sharepoint or in the SLK? And if so, how?

Thanks for your help!

Jul 13, 2012 at 9:14 AM

Hi everyone,

I am facing the same question. Has anyone managed to find how to get the time spent by a student on different training modules?

Thanks in advance,

Maria

Jul 16, 2012 at 5:19 PM

Wow, I'm in the same boat. We really want to be able to report on the amount of time a user spends in any given training module.

I hope we get some suggestions,

Dave

Jul 23, 2012 at 12:37 PM

For all i know, in SLK you can get the difference between training course started time and finished time.
Table AttemptItem, Columns: StartedTimestamp, FinishedTimestamp

for example select in db SLK (look column exec_time in format: dd:hh:mi:ss:ms):

SELECT     
	CASE 
		WHEN ati.id IS NULL THEN 0 
		ELSE ati.id 
	END COURSE_ID,
	asi.Title COURSE_NAME, 
	asi.CreatedBy INSTRUCTOR_ID,
	ui.Id LEARNER_ID,
	asi.PointsPossible POS_MARK,
	CONVERT(char, DateADD(ss, DATEDIFF(ss, GetUTCdate(), GETDATE()), asi.DateCreated), 104) DATE_SET, 
	CONVERT(char, DateADD(ss, DATEDIFF(ss, GetUTCdate(), GETDATE()), asi.DueDate), 104) DUE_DATE,
	CONVERT(char, DateADD(ss, DATEDIFF(ss, GetUTCdate(), GETDATE()), ati.StartedTimestamp), 104) COURSE_DATE_START, 
	CASE 
		WHEN FinishedTimestamp = CONVERT(date, '1753-01-01 04:00:00.000') THEN NULL 
		ELSE CONVERT(char, DateADD(ss, DATEDIFF(ss, GetUTCdate(), GETDATE()), ati.FinishedTimestamp), 104) 
	END COURSE_DATE_END,
	CASE 
		WHEN lai.FinalPoints IS NULL THEN ati.TotalPoints 
		ELSE lai.FinalPoints END GET_MARK, 
	CASE 
		WHEN IsFinal = 1 THEN +  RIGHT('0' + CONVERT(varchar(2), DATEDIFF(day, ati.StartedTimestamp, ati.FinishedTimestamp)), 2) + ':' + RIGHT('0' + CONVERT(varchar(6), DATEDIFF(second, ati.StartedTimestamp,ati.FinishedTimestamp)/3600), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, ati.StartedTimestamp,ati.FinishedTimestamp) % 3600) / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, ati.StartedTimestamp,ati.FinishedTimestamp) % 60), 2)
		ELSE NULL END EXEC_TIME 
FROM  dbo.AssignmentItem asi  
	LEFT JOIN LearnerAssignmentItem lai 
	ON lai.AssignmentId = asi.Id 
	LEFT JOIN AttemptStatus ats  
	ON ats.Id = lai.NonELearningStatus 
	LEFT JOIN AttemptItem ati  
	ON ati.LearnerAssignmentId = lai.Id AND ati.RootActivityId = asi.RootActivityId 
	LEFT JOIN SuccessStatus ss 
	ON ss.Id = ati.SuccessStatus  
	LEFT JOIN CompletionStatus cs  
	ON cs.Id = ati.CompletionStatus 
	LEFT JOIN UserItem ui ON ui.Id = lai.LearnerId
Aug 10, 2012 at 9:59 AM

Hi Dimitry,

thanks for the query. However it actually shows the difference between start and time which is not always the correct time for a stundet spent on a test.

For example  In case a student starts a test, works on it for 10 minutes and closes it, and then the next day opens it again for 10 minutes and completes it, the difference between start and finish will be more then 24 hours,, when in fact it is 20 minutes.

I see a column TotalTime in the ActivityAttemptItem table, but the values I get are pretty strange. Have you used it in any case?

Thanks

 

Aug 16, 2012 at 1:12 PM

Hi, mariaveledinova!

I don't use the column TotalTime because if the instructor will assign a course to himself, the value of this column always will be = 0.

But if you use only traditional scheme: instructor-learner, you can use it and as a result of your example totaltime value will be 20 minutes.

select in db SLK:

select  
convert(int, totaltime / 1000 / 60 / 60 / 60 / 24 / 365) count_days, 
convert(int, totaltime / 1000 / 60 / 60 / 60 / 24) count_hours,
convert(int, totaltime / 1000 / 60 / 60 / 60) count_minutes,
convert(int, totaltime / 1000 / 60 / 60) %60 count_seconds
from ActivityAttemptItem