Extracting Alps Data from ProSolution

Standard

Probably too late for anybody else wanting to get this done for this years A-Levels, but I thought I’d write up how we’ve got a data extract for the Alps Data working inside ProSolution.

In designing the system for achieving this we wanted to make it as automated as possible for calculating data, but still have the flexibility of completing manual adjustments.  Therefore, we decided to use user-defined fields for the student detail object, and enrolment object.  This way the automated system will update data in scope when the current value is null meaning that adjustments can be made and won’t be overwritten.

Step 1

The details about the Alps Software is available here: https://www.alps-va.co.uk/, it’s a service that compares students results at Level 3 (A-levels) with their GCSE results against other providers.  It helps understand how well students are achieving based on their prior performance at GCSE and against the national picture.

The first thing is to download and install their software from here https://my.alps-va.co.uk/Go/DownloadADCS. With this comes some useful files that we will need later. But for now just run the software and complete the registration bit, and eventually, you will end up with spreadsheet link screen that shows the data fields that are needed. Btw, It’s a great data validation tool.

Step 2

There are two pieces of information that we need to produce that are not standard from ProSolution, the students GCSE Score and the Alps Subject.

The first one to get working is the student GCSE Score.  For this,we will use the Qualification on Entry data held in ProSolution and a custom table with a mapping of Grades to scores.

Create a table ProSolutionReports to hold the mapping by copying and pasting this code.

USE [ProSolutionReports]
GO
/****** Object:  Table [dbo].[QonEScores]    Script Date: 16/08/16 20:37:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QonEScores](
	[QoEScoresID] [bigint] NULL,
	[LA_TYPE_CODE] [varchar](50) NULL,
	[Qual] [varchar](50) NULL,
	[Grade] [varchar](50) NULL,
	[QCA_Points] [bigint] NULL,
	[Rebased_QCA] [bigint] NULL,
	[QualCount] [bigint] NULL,
	[ALIS_Points] [bigint] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (127, N'1327', N'Key Skills Level 1', N'P', 1, 1, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (128, N'1327', N'Key Skills Level 2', N'F', 0, 0, 0, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (129, N'1327', N'Key Skills Level 2', N'P', 1, 1, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (130, N'1327', N'Key Skills Level 3', N'F', 0, 0, 0, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (131, N'1327', N'Key Skills Level 3', N'P', 1, 1, 0, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (132, N'2999', N'Short GCSE', N'A', 26, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (133, N'2999', N'Short GCSE', N'A*', 29, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (134, N'2999', N'Short GCSE', N'B', 23, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (135, N'2999', N'Short GCSE', N'C', 20, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (136, N'2999', N'Short GCSE', N'D', 17, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (137, N'2999', N'Short GCSE', N'E', 14, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (138, N'2999', N'Short GCSE', N'F', 11, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (139, N'2999', N'Short GCSE', N'G', 8, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (140, N'2999', N'Short GCSE', N'N', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (181, N'0029', N'BTEC First Diploma', N'ME', 196, 0, 4, 24)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (182, N'0029', N'BTEC First Diploma', N'PA', 160, 0, 4, 20)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (183, N'0029', N'BTEC First Diploma', N'D', 220, 0, 4, 30)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (184, N'0029', N'BTEC First Diploma', N'DS', 220, 0, 4, 30)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (184, N'0003', N'GCSE', N'AB', 98, 0, 0, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (185, N'1422', N'GCSE', N'AB', 98, 0, 2, 13)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (186, N'1422', N'GCSE', N'AC', 92, 0, 2, 12)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (187, N'1422', N'GCSE', N'AD', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (188, N'1422', N'GCSE', N'AE', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (189, N'1422', N'GCSE', N'AF', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (190, N'1422', N'GCSE', N'AG', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (191, N'1422', N'GCSE', N'BA', 98, 0, 2, 13)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (192, N'1422', N'GCSE', N'BC', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (193, N'1422', N'GCSE', N'BD', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (194, N'1422', N'GCSE', N'BE', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (195, N'1422', N'GCSE', N'BF', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (196, N'1422', N'GCSE', N'BG', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (197, N'1422', N'GCSE', N'CA', 92, 0, 2, 12)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (198, N'1422', N'GCSE', N'CB', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (199, N'1422', N'GCSE', N'CD', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (200, N'1422', N'GCSE', N'CE', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (201, N'1422', N'GCSE', N'CF', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (202, N'1422', N'GCSE', N'CG', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (203, N'1422', N'GCSE', N'DA', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (204, N'1422', N'GCSE', N'DB', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (205, N'1422', N'GCSE', N'DC', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (206, N'1422', N'GCSE', N'DE', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (207, N'1422', N'GCSE', N'DF', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (208, N'1422', N'GCSE', N'DG', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (209, N'1422', N'GCSE', N'EA', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (210, N'1422', N'GCSE', N'EB', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (211, N'1422', N'GCSE', N'EC', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (212, N'1422', N'GCSE', N'ED', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (213, N'1422', N'GCSE', N'EF', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (214, N'1422', N'GCSE', N'EG', 44, 0, 2, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (215, N'1422', N'GCSE', N'FA', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (216, N'1422', N'GCSE', N'FB', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (217, N'1422', N'GCSE', N'FC', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (218, N'1422', N'GCSE', N'FD', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (219, N'1422', N'GCSE', N'FE', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (220, N'1422', N'GCSE', N'GA', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (221, N'1422', N'GCSE', N'GB', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (222, N'1422', N'GCSE', N'GC', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (223, N'1422', N'GCSE', N'GD', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (224, N'1422', N'GCSE', N'GE', 44, 0, 2, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (225, N'1422', N'GCSE', N'GF', 38, 0, 2, 3)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (226, N'0003', N'GCSE', N'AB', 98, 0, 2, 13)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (227, N'0003', N'GCSE', N'AC', 92, 0, 2, 12)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (228, N'0003', N'GCSE', N'AD', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (229, N'0003', N'GCSE', N'AE', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (230, N'0003', N'GCSE', N'AF', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (231, N'0003', N'GCSE', N'AG', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (232, N'0003', N'GCSE', N'BA', 98, 0, 2, 13)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (233, N'0003', N'GCSE', N'BC', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (234, N'0003', N'GCSE', N'BD', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (235, N'0003', N'GCSE', N'BE', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (236, N'0003', N'GCSE', N'BF', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (237, N'0003', N'GCSE', N'BG', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (238, N'0003', N'GCSE', N'CA', 92, 0, 2, 12)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (239, N'0003', N'GCSE', N'CB', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (240, N'0003', N'GCSE', N'CD', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (241, N'0003', N'GCSE', N'CE', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (242, N'0003', N'GCSE', N'CF', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (243, N'0003', N'GCSE', N'CG', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (244, N'0003', N'GCSE', N'DA', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (245, N'0003', N'GCSE', N'DB', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (246, N'0003', N'GCSE', N'DC', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (247, N'0003', N'GCSE', N'DE', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (248, N'0003', N'GCSE', N'DF', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (249, N'0003', N'GCSE', N'DG', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (250, N'0003', N'GCSE', N'EA', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (251, N'0003', N'GCSE', N'EB', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (252, N'0003', N'GCSE', N'EC', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (253, N'0003', N'GCSE', N'ED', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (254, N'0003', N'GCSE', N'EF', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (255, N'0003', N'GCSE', N'EG', 44, 0, 2, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (256, N'0003', N'GCSE', N'FA', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (257, N'0003', N'GCSE', N'FB', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (258, N'0003', N'GCSE', N'FC', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (259, N'0003', N'GCSE', N'FD', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (260, N'0003', N'GCSE', N'FE', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (261, N'0003', N'GCSE', N'GA', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (262, N'0003', N'GCSE', N'GB', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (263, N'0003', N'GCSE', N'GC', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (264, N'0003', N'GCSE', N'GD', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (265, N'0003', N'GCSE', N'GE', 44, 0, 2, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (266, N'0003', N'GCSE', N'GF', 38, 0, 2, 3)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (1, N'2999', N'Short GCSE', N'U', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (2, N'0003', N'GCSE', N'U', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (3, N'0003', N'GCSE', N'A*A*', 116, 104, 2, 16)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (4, N'0003', N'GCSE', N'AA', 104, 92, 2, 14)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (5, N'0003', N'GCSE', N'BB', 92, 80, 2, 12)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (6, N'0003', N'GCSE', N'CC', 80, 68, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (7, N'0003', N'GCSE', N'DD', 68, 56, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (8, N'0003', N'GCSE', N'EE', 56, 44, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (9, N'0003', N'GCSE', N'FF', 44, 32, 2, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (10, N'0003', N'GCSE', N'GG', 32, 16, 2, 2)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (27, N'0029', N'BTEC First Diploma', N'M', 196, 0, 4, 24)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (28, N'0029', N'BTEC First Diploma', N'P', 160, 0, 4, 20)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (163, N'1422', N'GCSE', N'U', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (164, N'1422', N'GCSE', N'A*A*', 116, 104, 2, 16)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (165, N'1422', N'GCSE', N'AA', 104, 92, 2, 14)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (166, N'1422', N'GCSE', N'BB', 92, 80, 2, 12)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (167, N'1422', N'GCSE', N'CC', 80, 68, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (168, N'1422', N'GCSE', N'DD', 68, 56, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (169, N'1422', N'GCSE', N'EE', 56, 44, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (170, N'1422', N'GCSE', N'FF', 44, 32, 2, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (171, N'1422', N'GCSE', N'GG', 32, 16, 2, 2)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (172, N'1422', N'GCSE', N'A', 52, 46, 1, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (173, N'1422', N'GCSE', N'A*', 58, 52, 1, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (174, N'1422', N'GCSE', N'B', 46, 40, 1, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (175, N'1422', N'GCSE', N'C', 40, 34, 1, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (176, N'1422', N'GCSE', N'D', 34, 28, 1, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (177, N'1422', N'GCSE', N'E', 28, 22, 1, 3)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (178, N'1422', N'GCSE', N'F', 22, 16, 1, 2)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (179, N'1422', N'GCSE', N'G', 16, 8, 1, 1)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (180, N'1422', N'GCSE', N'N', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (72, N'0036', N'FE NVQ Level 1', N'F', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (73, N'0036', N'FE NVQ Level 1', N'P', 1, 1, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (74, N'0036', N'FE NVQ Level 2', N'F', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (75, N'0036', N'FE NVQ Level 2', N'P', 1, 1, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (88, N'0003', N'GCSE', N'A', 52, 46, 1, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (89, N'0003', N'GCSE', N'A*', 58, 52, 1, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (90, N'0003', N'GCSE', N'B', 46, 40, 1, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (91, N'0003', N'GCSE', N'C', 40, 34, 1, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (92, N'0003', N'GCSE', N'D', 34, 28, 1, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (93, N'0003', N'GCSE', N'E', 28, 22, 1, 3)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (94, N'0003', N'GCSE', N'F', 22, 16, 1, 2)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (95, N'0003', N'GCSE', N'G', 16, 8, 1, 1)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (96, N'0003', N'GCSE', N'N', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (97, N'0035', N'GNVQ Foundation', N'D', 136, 96, 4, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (98, N'0035', N'GNVQ Foundation', N'M', 112, 72, 4, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (99, N'0035', N'GNVQ Foundation', N'P', 76, 36, 4, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (100, N'0035', N'GNVQ Intermediate', N'D', 220, 84, 4, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (101, N'0035', N'GNVQ Intermediate', N'M', 196, 48, 4, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (102, N'0035', N'GNVQ Intermediate', N'PA', 160, 24, 4, 0)
GO

Step 3

Create a view that will calculate the GCSE Score based on the learner’s entry qualifications.


CREATE view [dbo].[Calculated_GCSE_Score]
as

SELECT DISTINCT [AcademicYearID], RefNo, StudentDetailID, Surname, FirstForename, [Age on 31 Aug], Sex,
CAST(SUM(Score) AS Decimal(19, 2)) AS Score, SUM(QualCount) AS QualCount,
CAST(CASE WHEN (SUM(QualCount) < 5) THEN SUM(Score) / SUM(QualCount) * (SUM(QualCount) / 5.0) ELSE CAST(SUM(Score) AS decimal(19, 2)) / CAST(SUM(QualCount) AS decimal(19, 2)) END AS Decimal(19, 2)) AS AvgQCAScore, CAST(SUM(ALIS) AS DECIMAL(19,2)) AS ALIS_Score, SUM(ALISCount) AS ALISCount, CASE WHEN SUM(ALIS) >0 THEN (CAST(CASE WHEN (SUM(ALISCount) < 5) THEN SUM(ALIS) / SUM(ALISCount) * (SUM(ALISCount) / 5.0)
ELSE CAST(SUM(ALIS) AS decimal(19, 2)) / CAST(SUM(ALISCount) AS decimal(19, 2)) END AS Decimal(19, 2))) ELSE 0 END AS ALISAvgScore
FROM
(

SELECT DISTINCT
S.AcademicYearID AS [AcademicYearID], S.RefNo AS RefNo, S.Surname, S.FirstForename, S.DateOfBirth AS [Date of Birth],
ProSolution.dbo.GetAgeOn31Aug(S.DateOfBirth, S.AcademicYearID) AS [Age on 31 Aug], S.Sex, S.StudentDetailID, S.StudentID,
LA.LEARNING_AIM_REF AS [Learning Aim Ref No], LA.LEARNING_AIM_TYPE_CODE, LA.LEARNING_AIM_TITLE AS [Learning Aim Title], QOE.Grade,
QS.QCA_Points AS Score, QS.QualCount, ISNULL(QS.ALIS_Points, 0) AS ALIS, CASE WHEN QS.ALIS_Points IS NOT NULL
THEN QS.QualCount ELSE 0 END AS ALISCount
FROM ProSolution.dbo.StudentDetail AS S
INNER JOIN ProSolution.dbo.QualsOnEntry AS QOE WITH (NOLOCK) ON QOE.StudentID = S.StudentID
INNER JOIN ProSolution.dbo.Learning_Aim AS LA WITH (NOLOCK) ON LA.LEARNING_AIM_REF = QOE.QualCode COLLATE database_default
INNER JOIN ProSolutionReports.dbo.QonEScores AS QS ON QOE.Grade = QS.Grade COLLATE database_default AND LA.LEARNING_AIM_TYPE_CODE = QS.LA_TYPE_CODE COLLATE database_default
) AS VrQonEScores

GROUP BY [AcademicYearID], RefNo, Surname, FirstForename, [Age on 31 Aug], Sex, StudentDetailID
GO

Step 4

Identify a student detail user-defined field for storing the Students GCSE Score.  In our system we used userdefined4 and create a procedure to update the field when it is null.


CREATE PROCEDURE [dbo].[SetGCSEScores]
-- Add the parameters for the stored procedure here
@AcademicYearId varchar(5)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

update sd
set sd.userdefined4 = calc.ALISAvgScore
FROM [ProSolutionReports].[dbo].[Calculated_GCSE_Score] calc
inner join ProSolution.[dbo].[StudentDetail] sd on calc.studentdetailid = sd.StudentDetailID
where calc.[AcademicYearID] = @AcademicYearId and [ALISAvgScore]>0 and sd.userdefined4 is null
END

Step 5

We wanted to be able to manage which learners were in the Alps extract from inside ProSolution. This meant being able to include and exclude learners and also include and exclude enrolments. We did this by using a user-defined field in Student Detail and another one in Enrolment. They were both created as List of Values UDFs with an options for Yes and No. The default is null. We will create a process that updates learners and enrolments to be included by setting the UDF to Yes where it is null. If we later decide that the learner or enrolment should not be included we set it to No.

UDF

We use Userdefined8 for the enrolment and Userdefined5 for the student detail.

Step 6

Now we create a procedure to update these UDFs. The student detail udf is update with the following:

create procedure [dbo].[SetStudentsEligibleForAlps]
as
update sd
set sd.userdefined5 = 1

  FROM [ProSolution].[dbo].[vStudentDetail] sd

  where sd.StudentDetailID in (
  select e.studentdetailid from
  [ProSolution].[dbo].[Enrolment] e
  inner join [ProSolution].[dbo].[Offering] o on e.offeringId = o.offeringid
  inner join [ProSolution].[dbo].[Learning_Aim] la on o.QualID = la.LEARNING_AIM_REF
  where e.FundingID=25 and la.NVQLevelID_v2 = '3'
  )
  and sd.userdefined5 is null

This scope is fairly brought, jsut all learners with EFA Funded enrolments at Level 3. We can manually exclude anything we don’t want.

For the enrolment flagging we use this stored procedure:

create procedure [dbo].[SetEnrolmentsEligibleForAlps]
as
update e
set e.userdefined8 = 1
    from
  [ProSolution].[dbo].[Enrolment] e
  inner join [ProSolution].[dbo].[Offering] o on e.offeringId = o.offeringid
  inner join [ProSolution].[dbo].[Learning_Aim] la on o.QualID = la.LEARNING_AIM_REF
  where e.FundingID=25 and la.NVQLevelID_v2 = '3' and e.userdefined8 is null
  and e.ExpectedEndDate<'20'+right(o.AcademicYearID,2)+'-08-01'

I’m sure there is a better way of doing the bit to detect if the enrolment is expected to end this year, but what’s there is working for now!

Step 7

Now we need to setup a mapping for the Offerings in ProSolution to an Alps Subject. Inside the folders that were installed by the Alps Software is Resources folder, C:\Program Files (x86)\Alps\Data collection software 2016\Files\Resources. The file called QAN to Alps mapping is the important one for this step. This contains a worksheet called QAN Only which contains a map of learning aims to Alps Subjects.

Import this into ProSolutionReports database using the SQL Management Studio Import Data function. I brought the data into a table called QAN_Alps_Mapping.

Step 8

THe second file that is included in the resources file is Alps Complete Subject List. We will import this into a table in ProSolutionReports, but we need to define the table first to create a unique identifier for each row.

CREATE TABLE [dbo].[alps_subject](
	[SubjectID] [int] IDENTITY(1,1) NOT NULL,
	[Subject Type] [varchar](max) NULL,
	[Subject Name] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Now import the spreadsheet to this table using the SQL Management Studio Data Import feature.

Step 9

Next, we need to identify a user-defined field on the offering table that we can use to store the Alps Subject. We use UserDefined8. To make this work properly we specified that the field would use a list of values. Then we populate that list using a bit of SQL, there is fair to much to type in!

INSERT INTO [ProSolution].[dbo].[_CCC_UserDefinedFieldLookupValues] (
      [DataSourceID]
      ,[DataSourceColumnName]
      ,[Value]
      ,[Description]
      ,[IsEnabled]
  )

select '46D38261-B36F-4FF1-8DA2-1300F512C3C4','UserDefined8',SubjectID, [Subject Name],1 from ProSolutionReports..alps_subject where
subjectid not in (
select value from ProSolution..[_CCC_UserDefinedFieldLookupValues]
where [DataSourceID] ='46D38261-B36F-4FF1-8DA2-1300F512C3C4'
)

You will need to get the correct GUID of the offering datasource, I’m not sure if it is the same across all installations. Use this bit of SQL to find yours

SELECT [DataSourceID] FROM [ProSolution].[dbo].[_CCC_DataSource] WHERE DataSourceName='Offering'

and if you used a different UDF make sure you update that too.

Step 10

Let’s now create a way to populate that Offering UDF with the subject code based on the QAN lookup table we create a moment ago. We use a stored procedure so we can run it regularly, ie as new offerings get added.

create procedure [dbo].[update_offering_alps_subjects]
as
update o
set
 o.userdefined8 = a.[SubjectID]
 from

ProSolution..Offering o
inner join [QAN_alps_mapping] m on o.QualID = cast(cast( m.[QAN] as int) as varchar)
inner join [alps_subject] a on a.[Subject Name] = m.[Alps Subject]

Step 11

Nearly there with getting the data in place. Lets schedule a job to run the stored procedures on a daily basis.

USE [msdb]
GO

/****** Object:  Job [Alps_data]    Script Date: 16/08/16 21:50:17 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 16/08/16 21:50:18 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Alps_data',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=0,
		@notify_level_netsend=0,
		@notify_level_page=0,
		@delete_level=0,
		@description=N'No description available.',
		@category_name=N'[Uncategorized (Local)]',
		@owner_login_name=N'CompassSystemUser', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Set GCSE Scores for 15/16]    Script Date: 16/08/16 21:50:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Set GCSE Scores for 15/16',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=4,
		@on_success_step_id=2,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXEC	 [dbo].[SetGCSEScores]
		@AcademicYearId = N''15/16''
',
		@database_name=N'ProSolutionReports',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Update offerings with Alps Subject]    Script Date: 16/08/16 21:50:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update offerings with Alps Subject',
		@step_id=2,
		@cmdexec_success_code=0,
		@on_success_action=4,
		@on_success_step_id=3,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXEC	 [dbo].[update_offering_alps_subjects]',
		@database_name=N'ProSolutionReports',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Set Students Eligiable for Alps]    Script Date: 16/08/16 21:50:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Set Students Eligiable for Alps',
		@step_id=3,
		@cmdexec_success_code=0,
		@on_success_action=3,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXECUTE  [dbo].[SetStudentsEligibleForAlps] ',
		@database_name=N'ProSolutionReports',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Set Enrolments Eligible for Alps]    Script Date: 16/08/16 21:50:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Set Enrolments Eligible for Alps',
		@step_id=4,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXECUTE  [dbo].[SetEnrolmentsEligibleForAlps] ',
		@database_name=N'ProSolutionReports',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily @ 0600',
		@enabled=1,
		@freq_type=4,
		@freq_interval=1,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=0,
		@active_start_date=20160815,
		@active_end_date=99991231,
		@active_start_time=60000,
		@active_end_time=235959,
		@schedule_uid=N'0d4e85cf-4eb4-442a-ba8e-fb36deb19208'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Step 12

We’re nearly there, all the data is in place (assuming you’ve run the job that was just created), we just need a view to bring the data together and then import it as a datasource into ProSolution. Here’s the view we need:

CREATE view [dbo].[Alps_Export]
as

select sd.[AcademicYearId]
       ,[RefNo]
      ,[Surname]
      ,[FirstForename]
	  ,ProSolution.dbo.GetAgeOn31Aug([DateOfBirth],sd.[AcademicYearID]) - 4 as [Study Year]
	  ,'20'+right(sd.[AcademicYearId],2) as Year
	  ,sd.Sex
      ,[DateOfBirth]
	  , sd.EthnicGroup
	  , null as QCA_Score
      ,sd.[UserDefined4] as GCSE_Score
	  , sub.[Subject Type]
	  , sub.[Subject Name]
      , e.Grade
  FROM [ProSolution].[dbo].[vStudentDetail] sd
  inner join [ProSolution].[dbo].[Enrolment] e on e.StudentDetailID = sd.StudentDetailID
  inner join [ProSolution].[dbo].[Offering] o on e.offeringId = o.offeringid
  inner join [ProSolution].[dbo].[Learning_Aim] la on o.QualID = la.LEARNING_AIM_REF
  left join [ProSolutionReports].[dbo].[alps_subject] sub on sub.subjectID = o.UserDefined8
  where e.userDefined8 = '1' and ProSolution.dbo.GetAgeOn31Aug([DateOfBirth],sd.[AcademicYearID]) - 4 <15
  and sd.userdefined5 = '1'

There are two ProSolution Definition files to import, firstly the datasource:
https://raw.githubusercontent.com/miketrebilcock/AlpsExport/master/Alps_Export.cccds.xml

then the report:
https://raw.githubusercontent.com/miketrebilcock/AlpsExport/master/Alps%20Export.cccrpt.xml

Finished!

You should now be able to manage which learners appear in the report and export the data into a spreadsheet for importing into the Alps Tool. You’ll probably find a number of offerings will need to have the Alps Subject set manually, you can see which ones are blank from the report.

Hope you managed to follow these instructions and found them useful! All the code here is available on a github repo here:

https://github.com/miketrebilcock/AlpsExport

Must forget, a massive thank you to Asmin at Middlesborough College for sharing how they achieve this and giving me the starting point.

 

*usually disclaimers apply, follow at your own risk, make sure you know what you are doing, perhaps try it on training first, etc, etc.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s