ProSolution Student Export View (Template)

Created by Chris Rowe, Modified on Fri, 12 Jun at 1:07 PM by Chris Rowe

This is a template Navigate view for colleges running ProSolution. It provides the basic student, tutor and attribute data required for Navigate's external database, and is intended as a starting point that you adapt to your own college's data and processes.

About this template

Script name: [ProSolutionReports].[dbo].[NavigateStudentsExport]
Description: a template Navigate view (ProSolution) that provides the basics for the data required for Navigate external databases.
Version: 1.0, created by Navigate on 22/03/25 (initial development).

Important notes before you use it

This script may not provide data that is specific to your college's processes, so you may need to alter it so the data matches what you require.
Student email: there is no specific place for student emails in ProSolution, so the template uses UserDefined1. Amend this to wherever you store this data, or use a user-defined field on the student record and change the number on [UserDefined1] to match the field you selected.
Attributes: attributes differ from college to college, so you may need to move some around to fit your purpose. See the MIS SQL Technical document for the available columns you can use in this template – copy and paste from that document, ensuring the AS Attribute alias remains after each column.
Funding filter: the template filters on FundingID 25 (16-19 EFA) and 35 (Adult Skills). Amend by commenting out or deleting what is not required.
Excluding offerings: you can exclude specific offerings by offering code or offering SID using the commented-out sections in the offering sub-query.
Academic year & mode: the academic-year filter (defaulting around 22/08) and the ModeOfAttendanceID = '01' (Full Time Course) filter can be amended to fit your needs.

The template view

/**
--------------------------------------------------------------------------------------------------------------------------------------------------
Script Name: [ProSolutionReports].[dbo].[NavigateStudentsExport] 

Description: Template Navigate View (ProSolution)

This is a template view is designed to provide you with the basics for the data required for Navigate external databases.

Important Note:

It is important to note that this script may not provide data that is 
college process specific, therefore, you may need to alter it so the data 
matches what you require.

Version:  1.0
Created By: Navigate
Created Date: 22/03/25

Change Log

22/03/25 - Initial Development

 ---------------------------------------------------------------------------------------------------------------------------------------------------
**/

DECLARE @AcademicYearID AS VARCHAR(5) = CASE WHEN GETDATE() < DATEFROMPARTS('20' + left([ProSolution].[dbo].[GetAcademicYear](getdate()),2), 8, 22) 
			THEN [ProSolution].[dbo].[GetAcademicYear](DATEADD(year, -1, GETDATE()))
				ELSE [ProSolution].[dbo].[GetAcademicYear](GETDATE()) END

SELECT 
	LTRIM(RTRIM(SD.[RefNo])) AS StudentNumber --Trim any whitespace
	,COALESCE(SD.KnownAs, SD.FirstForename) FirstForename -- This get students known as name first if blank get their firstforename
	,SD.[Surname] AS StudentSurname
	,SD.[AcademicYearID]

	/*
	As there is no specific place for student emails in ProSolution.
	You may need to amend the below to where you store this data.
	Alternatively, you can use a user defined field in the student record
	to store their student email and alter the number at the end of [UserDefined1]
	to match the field you've selected.
	*/
	,SD.[UserDefined1] AS Email
	,SD.[UserDefined1] AS StudentUsername

	--Tutor assigned to students main offering
	,ISNULL(vMain.[Firstname], 'Temp') AS TutorForename
	,ISNULL(vMain.[Surname], 'Late') AS TutorSurname
	,ISNULL(sU.[EmailAddress], 'template@email.ac.uk') as TutorEmail

	/*
	Attributes differ from college to college, may need to move some around to fit your purpose.
	Please see MIS SQL Technical document for some avaliable columns for this template you can use.
	Just copy and paste from the document ensuring the AS Attribute remains after.
	*/
	,vMain.[Level1Description] as Attribute1
	,vMain.[Level1Code] AS Attribute1Code
	,vMain.[Level2Description] as Attribute2
    ,vMain.[Level2Code] AS Attribute2Code
	,CONCAT(COALESCE(OG.[OfferingCode], vMain.[OfferingCode]),' - ', COALESCE(OG.[OfferingName], vMain.[OfferingName])) as Attribute3
	,'' Attribute3Code
	,CONCAT(COALESCE(OG.[OfferingCode], vMain.[OfferingCode]),'-', COALESCE(OG.[OfferingGroupCode],'A')) as Attribute4
	,'' as Attribute4Code
	,G.[Description] as Attribute5
	,SD.[Sex] as Attribute5Code
	,EG.[Description] as Attribute6
	,SD.[EthnicGroupID] as Attribute6Code
	/*End of Attributes*/

	,1 AS Active
	,0 as ImportID
	,CASE 	  
	--1 is ACTIVE, 2 is CLOSED and NULL no action
		WHEN SD.[HasEducationHealthCarePlan] = 1 THEN 'ACTIVE'
		WHEN auT.EHCPChanged = 1 AND SD.[HasEducationHealthCarePlan] = 1 THEN 'ACTIVE'
		WHEN auT.EHCPChanged = 2 AND SD.[HasEducationHealthCarePlan] = 0 THEN 'CLOSED'
	ELSE NULL 
	END AS [EHCPStatus]
	,SD.[StudentDetailID] AS ExternalID

FROM [ProSolution].[dbo].[StudentDetail] AS SD with (nolock)

-- This join takes data from the students main offering
LEFT JOIN
(
	SELECT
		vMO.[StudentDetailID],
		vMO.[OfferingID],
		vMO.[OfferingCode],
		vMO.[OfferingName],
		vMO.[EnrolmentID],
		vMO.[SID],
		vMO.[Code], -- College Level Code
		vMO.[Name], --Course Name
		vMO.[SiteDescription], --Site Name
		E.[OfferingGroupID],
		E.[CompletionStatusID],
		S.[Firstname], --Staff Firstname
		S.[Surname], --Staff Surname
		S.[SystemUserID], -- Staff User ID
		CL.[Level1Code],
		CL.[Level1Description],
		CL.[Level2Code],
		CL.[Level2Description]
	FROM 
		[ProSolution].[dbo].[vStudentDetailMainOffering] AS vMO with (nolock)  

	LEFT JOIN 
	(
		SELECT 
			[OfferingID] 
			,MAX(StaffID) as StaffID
		FROM 
			[ProSolution].[dbo].[OfferingStaff] AS OS with (nolock) 
		GROUP BY [OfferingID]
	) ST
		ON vMO.[OfferingID] = ST.[OfferingID]

	LEFT JOIN [ProSolution].[dbo].[Staff] AS S with (nolock) 
		ON ST.[StaffID] = S.[StaffID]

	LEFT JOIN  [ProSolution].[dbo].[vCollegeLevel] as CL with (nolock)
		ON CL.[SID] = vMO.[SID]

	LEFT JOIN [ProSolution].[dbo].[Enrolment] as E with (nolock)
		ON E.[EnrolmentID] = vMo.[EnrolmentID]
) as vMain
	ON SD.[StudentDetailID] = vMain.[StudentDetailID]

--Staff Username (Email Address)
LEFT JOIN [ProSolution].[dbo].[_CCC_SystemUser] AS sU  with (nolock)
	ON sU.[SystemUserID] = vMain.[SystemUserID] 

-- Gets students main offering group ID
LEFT JOIN [ProSolution].[dbo].[vOfferingGroups] AS OG with (nolock)
	ON vMain.[OfferingGroupID] = OG.[OfferingGroupID]

--Gets student gender
LEFT JOIN [ProSolution].[dbo].[Gender] AS G with (nolock)
	ON SD.[Sex] = G.[GenderID]

--Gets student ethnic group
LEFT JOIN [ProSolution].[dbo].[EthnicGroup] AS EG with (nolock)
	ON SD.[EthnicGroupID] = EG.[EthnicGroupID]

-- Offering list sub query filter, can be altered to exlude specific offerings from view if required.
LEFT JOIN 
(
SELECT							
	DISTINCT O.[OfferingID], O.[QualID]
FROM
	[ProSolution].[dbo].[Offering] O with (nolock)

	INNER JOIN [ProSolution].[dbo].[Enrolment] E with (nolock)
		ON O.[OfferingID] = E.[OfferingID]
		--Amend [FundingID] as needed by commenting out or deleting what is not required.
		AND E.[FundingID] IN (25, 35) -- (25) = 16-19 EFA and (35) = Adult Skills

		/* If you'd like to exclude offerings uncomment below the AND, 
		add offering code to list within the () ensuring they are enclosed within '' 
		followed by a , to seperate each */
		
		--AND O.[Code] NOT IN ()

		/* You can also use the offering SID code to exclude.
			Uncomment the below to use, ensure the code is within () and followed by a , to seperate each */
		
		-- AND O.[SID] NOT IN ()

	INNER JOIN 
	(
		SELECT 
		*
		FROM 
		[ProSolution].[dbo].[CollegeLevel] with (nolock)

		WHERE 
			[Enabled]=1
		AND [IsAcademic] = 1
		-- Filter to amend academic year automatically for the 22/08 you may want to change this to fit the date you'd like
		AND (MaxAcademicYearID >= @AcademicYearID OR MaxAcademicYearID IS NULL) 

	) as vCL
	on O.[SID] = vCL.[SID]
	--Amend ModeOfAttendanceID as needed by commenting out or deleting what is not required.
	AND ModeOfAttendanceID = '01' -- Full Time Course
	OR ModeOfAttendanceID = '15' -- Part Time Course

) Olist
on vMain.[OfferingID] = Olist.[OfferingID]

-- Get course level from learning aim.
LEFT JOIN (
	
	SELECT
		LA.[NOTIONAL_NVQ_LEVEL_CODE] AS CourseLevel
		,O.[OfferingID]
		FROM [ProSolution].[dbo].[Learning_Aim] AS LA with (nolock)

	LEFT JOIN [ProSolution].[dbo].[Offering] AS O with (nolock)
		ON O.[QualID] = LA.[LEARNING_AIM_REF]

	WHERE O.[OfferingID] IS NOT NULL

) AS lvl
	ON  vMain.[OfferingID] = lvl.[OfferingID]

--This join checks for changes the audit trail for changes to  education health care plan which assists in opening and closing cases automatically.
LEFT JOIN (
SELECT
	  SD.[StudentID]
	  ,SD.[StudentDetailID]
      ,C_AT.[TableName]
      ,C_AT.[WhereClause]
      ,C_AT.[RowDescription]
	  --1 is ACTIVE, 2 is CLOSED and 3 is no action (NULL) this checks changes and closes the EHCP plan on navigate side based on audit trail changes in prosol.
	  ,CASE 
		WHEN [ChangeInfo] LIKE '%HasEducationHealthCarePlan: Changed from ''False'' to ''True''%' THEN 1
		WHEN [ChangeInfo] LIKE '%HasEducationHealthCarePlan: Changed from '''' to ''True''%' THEN 1
		WHEN [ChangeInfo] LIKE '%HasEducationHealthCarePlan: Changed from ''True'' to ''False''%' THEN 2
		WHEN [ChangeInfo] LIKE '%HasEducationHealthCarePlan: Changed from '''' to ''False''%' THEN NULL
	   END AS [EHCPChanged]
      ,C_AT.[ModifiedOn]
  FROM [ProSolution].[dbo].[_CCC_AuditTrail] as C_AT with (nolock)
  
 INNER JOIN [ProSolution].[dbo].[StudentDetail] as SD with (nolock)
	ON SUBSTRING(C_AT.[WhereClause],CHARINDEX('=',C_AT.[WhereClause])+2, LEN(C_AT.[WhereClause])) = SD.[StudentDetailID]

INNER JOIN
(
	SELECT
	    [StudentID],
		[WhereClause],
		MAX([ModifiedOn]) AS maxDate
	FROM [ProSolution].[dbo].[_CCC_AuditTrail] with (nolock)
		INNER JOIN [ProSolution].[dbo].[StudentDetail] as SD with (nolock)
	ON SUBSTRING([WhereClause],CHARINDEX('=',[WhereClause])+2, LEN([WhereClause])) = [StudentDetailID]
		WHERE [TableName] = 'StudentDetail' AND [ChangeInfo] LIKE '%HasEducationHealthCarePlan%'
	GROUP BY [WhereClause], [StudentID]
)ASX
 ON X.[StudentID] = SD.[StudentID] AND X.[maxDate] = C_AT.[ModifiedOn]

WHERE 
C_AT.[TableName] = 'StudentDetail' 
AND C_AT.[ChangeInfo] LIKE '%HasEducationHealthCarePlan%'
AND C_AT.[ProductName] = 'ProSolution'
AND SD.[AcademicYearID] >= '23/24' -- Amend year if needed

) AS auT
	ON auT.[StudentID] = SD.[StudentID]
--End of audit trail join

-- This join checks that the student has at least one Enrolment that is continuing.
LEFT JOIN ( 
	SELECT 
		E.[StudentDetailID] AS [StudentDetailID],
		COUNT(DISTINCT E.[CompletionStatusID]) AS [CompletionStatusIDCount]
	FROM [ProSolution].[dbo].[Enrolment] AS E with (nolock)
		INNER JOIN [ProSolution].[dbo].[StudentDetail] AS SD with (nolock)
			ON SD.[StudentDetailID] = E.[StudentDetailID]
		WHERE E.[CompletionStatusID] = 1 AND SD.[AcademicYearID] >= '23/24' -- amend year if required
	GROUP BY 
		E.[StudentDetailID]
	HAVING COUNT(DISTINCT E.[CompletionStatusID]) > 0
) AS isCon
	ON isCon.[StudentDetailID] = vMain.[StudentDetailID]

WHERE 
SD.AcademicYearID = @AcademicYearID
				-- Filters for academic year - specifically changing around 22/08 amend as needed.
AND isCon.[CompletionStatusIDCount] > 0 -- Confirm student has at least one enrolment which is continuing.

Source document attached below.

Attachments (1)

docx

StudentExport-View-fb6c70.docx
19.6 KB