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?
回覆 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 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!