SQL JUPUS-student academic performance

May I have a sql for extracting data for checking/adjusting student academic performance in the following order:
subject,classname,classno,chname,enname,score,om,percentile,rank
sorted on subject, om
Thx a lot.

 

This sql is for when generate is run for a single assessment calculation.

select
a4.ch_des,
a.classcode,
a.classno,
a.chname,
a.enname,
a3.SYSPERCSCORE,
a3.OMCLASSLVL,
a1.PERCENTILE,
a1.OVERALLRATING
from vw_stu_lateststudent a
join TB_SLP_ACDMC_PERF a1
on a.suid=a1.suid and a.schyear = a1.schyear and a.stuid = a1.stuid
join TB_SLP_SUBJ_MAPPING a2
on a1.suid=a2.suid and a1.schyear = a2.schyear and a1.DSESUBJCODE = a2.DSESUBJCODE
left outer join tb_asr_subjassessdata a3
on a.suid=a3.suid and a.stuid=a3.stuid and a2.subjcode=a3.subjcode and
a.schyear=a3.schyear and a.schlvl=a3.schlevel and a.schsess=a3.schsession and a.classlvl=a3.classlevel and a2.timeseq=a3.timeseq
left outer join tb_hse_common a4
on a.suid=a4.suid and a4.TB_ID='SBJ' and a4.CODE_ID=a3.SUBJCODE
where a.schyear=?
order by 1,7

 

tried...could the percentile be expressed in the range between 1 to 100? So that teachers may adjust the setting with reference to this summary easily.....thx a lot.

 

Pls try this SQL

select
a4.ch_des,
a.classcode,
a.classno,
a.chname,
a.enname,
a3.SYSPERCSCORE,
a3.OMCLASSLVL||'/'||a3.OMCLASSLVLBASE 'OM Classlevel',
(case when a3.OMCLASSLVL = a3.OMCLASSLVLBASE then 100
when a3.OMCLASSLVLBASE > 0 then ((a3.OMCLASSLVL*100) / a3.OMCLASSLVLBASE) else 0 end) '%',
a1.PERCENTILE,
a1.OVERALLRATING
from vw_stu_lateststudent a
join TB_SLP_ACDMC_PERF a1
on a.suid=a1.suid and a.schyear = a1.schyear and a.stuid = a1.stuid
join TB_SLP_SUBJ_MAPPING a2
on a1.suid=a2.suid and a1.schyear = a2.schyear and a1.DSESUBJCODE = a2.DSESUBJCODE
left outer join tb_asr_subjassessdata a3
on a.suid=a3.suid and a.stuid=a3.stuid and a2.subjcode=a3.subjcode and
a.schyear=a3.schyear and a.schlvl=a3.schlevel and a.schsess=a3.schsession and a.classlvl=a3.classlevel and a2.timeseq=a3.timeseq
left outer join tb_hse_common a4
on a.suid=a4.suid and a4.TB_ID='SBJ' and a4.CODE_ID=a3.SUBJCODE
where a.schyear=?
order by 1,7

 

Tried with 2010 S5 data....After generating student performance ranking from the SRR module....I can print report from this module with the built in report...but no data of ch_des, syspercscore, omclasslevel and % can be extracted with this sql...all are blanked....Can it extract data generated from previous year, not 2011....Thx a lot.

 

How about the sql for generating combining two assessments of 2010 S5 final and 2011 S6 1st term data together?

 

回覆 5# st888888


SSR module can only generate data for S6, and sql will not work on S5.

 

回覆 6# Tom


Pls try this SQL:

select
b.CLASSCODE,
b.CLASSNO,
a.ENNAME,
a.CHNAME,
d.ch_des,
(select g.sysscore ||' , '||g1.sysscore||' , '||
(case when g.sysscore > 0 then g.sysscore else 0 end) + (case when g1.sysscore > 0 then g1.sysscore else 0 end)
from wsadmin.TB_ASR_SUBJASSESSDATA g
left outer join wsadmin.TB_ASR_SUBJASSESSDATA g1
on a.SUID = g1.SUID and a.SCHLVL = g1.SCHLEVEL and a.SCHSESS = g1.SCHSESSION and
g1.SCHYEAR = b.SCHYEAR-1 and a.STUID = g1.STUID and g1.SUBJCODE = c.SUBJCODE and
g1.TIMESEQ = 1000
where a.SUID = g.SUID and a.SCHLVL = g.SCHLEVEL and a.SCHSESS = g.SCHSESSION and
g.SCHYEAR = b.SCHYEAR and a.STUID = g.STUID and g.SUBJCODE = c.SUBJCODE and
g.TIMESEQ = 1100)
from wsadmin.TB_STU_STUDENT a
join wsadmin.VW_STU_LATESTSTUSCHREC b
on a.SUID = b.SUID and a.STUID = b.STUID and b.CLASSLVL = 'S6' and b.SCHYEAR = ?
left outer join wsadmin.TB_ASR_SUBJASSESSDATA c
on a.SUID = c.SUID and a.SCHLVL = c.SCHLEVEL and a.SCHSESS = c.SCHSESSION and
b.SCHYEAR = c.SCHYEAR and a.STUID = c.STUID and c.TIMESEQ = 1100
left outer join tb_hse_common d
on a.suid=d.suid and d.TB_ID='SBJ' and d.CODE_ID=c.SUBJCODE
order by 1,2

 

Actually, my school needs to use the JUPAS APPLICATION--School Reference Report-Gen Academic Performance Info to generate results from two asssessments.

I want to use S5 annual results(weighting 60%) and S6 1st term test results(weighting 40%) to combine together.
The output format should be
Application Number School Code HKID Last Name First Name Class Student Number Subject Code Percentile Overall Rating
Could you help to modify the above SQL?

Thanks

 

the requested function will be available in feb, and it is too complicated to get that data by sql.

 

The SQL in 4# is very useful. How can I include the 'grade' after the 'score' column?
In my school, we decided to use the S6 T1A1 grades for overall rating.
Thanks a lot!

 

just add the below after the "a3.SYSPERCSCORE,"

SYSGRADECONVCOMPCODE,

 

I got it.
Thanks a lot!

 

only classcode, classno, enname, chname can be extracted...other fields are blank...pls help

 

you need to run consolidation first.