SQL JUPUS-student academic performance

本文由 st888888 在 2012-01-19 發表於 "WebSAMS 討論區" 討論區

  1. 137316

    st888888
    Expand Collapse

    文章:
    21
    讚:
    0
    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.
     
    #1 st888888, 2012-01-19
  2. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    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
     
    #2 edb-石頭, 2012-01-20
  3. 137316

    st888888
    Expand Collapse

    文章:
    21
    讚:
    0
    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.
     
    #3 st888888, 2012-01-20
  4. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    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
     
    #4 edb-石頭, 2012-01-26
  5. 137316

    st888888
    Expand Collapse

    文章:
    21
    讚:
    0
    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.
     
    #5 st888888, 2012-01-27
  6. 10268969

    Tom
    Expand Collapse

    文章:
    81
    讚:
    0
    How about the sql for generating combining two assessments of 2010 S5 final and 2011 S6 1st term data together?
     
  7. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    回覆 5# st888888


    SSR module can only generate data for S6, and sql will not work on S5.
     
    #7 edb-石頭, 2012-01-30
  8. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    回覆 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
     
    #8 edb-石頭, 2012-01-30
  9. 10268969

    Tom
    Expand Collapse

    文章:
    81
    讚:
    0
    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
     
  10. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    the requested function will be available in feb, and it is too complicated to get that data by sql.
     
    #10 edb-石頭, 2012-01-30
  11. 55031611

    草草子
    Expand Collapse

    文章:
    21
    讚:
    0
    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!
     
    #11 草草子, 2012-01-31
  12. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    just add the below after the "a3.SYSPERCSCORE,"

    SYSGRADECONVCOMPCODE,
     
    #12 edb-石頭, 2012-02-01
  13. 55031611

    草草子
    Expand Collapse

    文章:
    21
    讚:
    0
    I got it.
    Thanks a lot!
    :laughing:
     
    #13 草草子, 2012-02-01
  14. 137316

    st888888
    Expand Collapse

    文章:
    21
    讚:
    0

    only classcode, classno, enname, chname can be extracted...other fields are blank...pls help
     
    #14 st888888, 2013-01-22
  15. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    you need to run consolidation first.
     
    #15 edb-石頭, 2013-01-22