Query 1
with alps_cte (reference, surname,forenames,m_reference,level,Class,subject,division, gender,SEN, IMD_Quartile,ethnicity, MEG_grade,average_gcse_points,cap_gradeQ1,high_grade,Pass,
expected_alps_points,actual_alps_points)
as
(
SELECT
p.p_reference AS reference
,p.p_surname as surname
, p.P_forenames AS forenames
,m.m_reference
,m.m_level as level
, cm.m_reference as Class
, SUBSTRING(cm.m_name, 0, charindex(',', cm.m_name, 0)) as 'Subject'
,substring(m.m_reference,4,1) as division
,p.p_gender as gender
,' ' as 'SEN'
,case
when pf_flag =51 then Right(f.f_reference,1)
when pf_flag =52 then Right(f.f_reference,1)
when pf_flag =53 then Right(f.f_reference,1)
when pf_flag =54 then Right(f.f_reference,1)
end as 'IMD Quartile'
,lc.c_name as ethnicity
, tg.target_grade AS MEG_grade
,CAST( AVG( t.points ) AS NUMERIC( 6, 1 ) ) AS average_gcse_points
,am.am_grade as cap_gradeQ1
,case when am.am_grade ='A*' then 'Yes'
when am.am_grade ='A' then 'Yes'
when am.am_grade ='B' then 'Yes'
else 'No'
end as 'high grade'
,case when am.am_grade ='U' then 'No'
when am.am_grade ='F' then 'No'
else 'Yes'
end as 'Pass'
,case when CAST( AVG( t.points ) AS NUMERIC( 6, 1 ) ) >=7.75 then 124
when CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) >=7 and CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) <7.75 then 109.82
when CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) >=6.55 and CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) <7 then 101.33
when CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) >=6.1 and CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) <6.55 then 95
when CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) >=5.65 and CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) <6.1 then 89.52
when CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) >=5.21 and CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) <5.65 then 83.75
when CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) >=4.77 and CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) <5.21 then 79
when CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) >=4.37 and CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) <4.77 then 74.89
when CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) >=3.79 and CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) <4.37 then 70
when CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) >=3.05 and CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) <3.79 then 65.71
when CAST( AVG( t.points ) AS NUMERIC( 6, 1 )) <3.05 then 60.77
end as 'expected_alps_points'
,case when am.am_grade ='A*' then 140.00
when am.am_grade ='A' then 120.00
when am.am_grade ='B' then 100.00
when am.am_grade ='C' then 80.00
when am.am_grade ='D' then 60.00
when am.am_grade ='E' then 40.00
when am.am_grade ='U' then 0
end as 'actual_alps_points'
FROM
dbo.vw_module m
LEFT OUTER JOIN dbo.vw_moduleRelation mr
ON mr.mr_parent = m.m_id
LEFT OUTER JOIN dbo.vw_module cm
ON cm.m_id = mr.mr_child
INNER JOIN dbo.flx_markbookAssignment a
ON m.m_id = a.a_module
INNER JOIN dbo.flx_markbookCommonAssessmentPointDeadline capd
ON capd.capd_id = a.a_capDeadline
INNER JOIN dbo.flx_markbookAssignmentType at
ON at.at_id = a.a_type
AND at.at_commonAssessmentPoint =1
INNER JOIN dbo.vw_enrolment e
ON e.e_module = COALESCE( cm.m_id, m.m_id )
INNER JOIN dbo.vw_person p
ON p.p_id = e.e_person
LEFT OUTER JOIN dbo.flx_markbookAssignmentMark am
ON am.am_assignment = a.a_id
AND am.am_person = p.p_id
AND am.am_active = 1
LEFT OUTER join dbo.vw_personFlag pf
ON p.p_id = pf.pf_person
INNER JOIN dbo.flx_flag f
ON pf.pf_flag = f.f_id
INNER JOIN dbo.flx_enrolmentStatus es
ON es.es_id = e.e_status
AND
(
es.es_enrolled = 1
)
INNER JOIN vw_student s
ON p.p_id = s.s_id
INNER JOIN flx_studentStatus ss
ON s.s_status = ss.ss_id
INNER JOIN dbo.flx_lookupCodes lc
ON lc.c_code = p.p_ethnicity
AND lc.c_type = 'ethnicity'
AND lc.c_current = 1
INNER JOIN dbo.vw_personQualification pq
on p.p_id =pq.pq_person
OUTER APPLY dbo.flx_fn_studentTargetGrades( p.p_id, m.m_id, DEFAULT ) tg
CROSS APPLY (
SELECT TOP 1
q.q_id
FROM
dbo.vw_qualifications q
WHERE
q.q_name = pq.pq_name
AND q.q_gcseflag = 1
and ss.ss_enrolled = 1
) q
CROSS APPLY dbo.flx_fn_gradeGcsePoints( COALESCE( pq.pq_outcome, pq.pq_expectedOutcome ),null) t
WHERE
p.p_reference like 'SY%'
and (cm.m_reference like '21-%' )
and (m.M_reference not like '%2')
and (m.M_reference not like '%1')
and m.m_reference not like '%1'
and m.m_reference not like '%2'
and m.m_reference not like '%U6%'
and m.m_reference not like '%L6%'
and m.m_reference not like '%LU%'
and m.m_reference not like '%TS%'
and m.m_reference not like '%HA%'
and m.m_reference not like '%E3%'
and m.m_reference not like '%L3%'
and m.m_reference not like '%PM'
and pf.pf_active = 1
and pf_flag in (51,52,53,54)
AND pf.pf_start <= GETDATE()
AND ( pf.pf_end IS NULL OR pf.pf_end >= GETDATE() )
--and m.m_reference not like '%2'
AND a.a_capDeadline=23
and m.m_reference ='20-AD-AH-AL'
group by p.p_reference,p.p_surname,p.p_forenames,p.p_preferredname
,m.m_reference
,m.m_name
,cm.m_name
,m.m_level
, cm.m_reference
,p.p_ethnicity
,p.p_gender
,pf.pf_flag
,f.f_reference
,lc.c_name
, tg.target_grade,
am_grade
)
select round(sum(actual_alps_points-expected_alps_points)/(count(reference)*100) + 1,2) as alps_score
from alps_cte
group by m_reference
--declare @alps_score float =alps_score
Query 2
SELECT top 1 alps_grade as alps_grade
,[alps_score]
FROM [CEDAR].[dbo].[flx_alps_boundaries]
where course_code ='20-ad-ah-al'
group by alps_grade,alps_score
order by abs(@alps_score-alps_score)