學生資料 想加埋 最後出席日期

本文由 Alan CKC 在 2022-03-09 發表於 "WebSAMS 討論區" 討論區

  1. 57223717

    Alan CKC
    Expand Collapse

    文章:
    11
    讚:
    0
    想加埋 最後出席日期

    SELECT A.REGNO, A.SCHLVL, B.EN_DES, B.CH_DES, A.CLASSNO, D.CLASSNAME, A.SUBJGROUP, A.ENNAME, A.CHNAME, A.SEX, A.CCC, DATEFORMAT(A.DOB, 'DD/MM/YYYY') AS DOB, A.STRN, A.BIRTHCERT, A.HKID, A.SCHHOUSE,e.ch_des 'Status'
    FROM VW_STU_LATESTSTUDENT A
    LEFT OUTER JOIN TB_HSE_COMMON B
    ON B.SUID = A.SUID AND B.TB_ID = 'SCHLVL' AND B.CODE_ID = A.SCHLVL
    LEFT OUTER JOIN TB_HSE_COMMON C
    ON C.SUID = A.SUID AND C.TB_ID = 'SESSIO' AND C.CODE_ID = A.SCHSESS
    LEFT OUTER JOIN TB_SCH_SCHCLASS D
    ON A.SUID = D.SUID AND A.SCHYEAR = D.SCHYEAR AND A.SCHLVL = D.SCHLEVEL AND A.SCHSESS = D.SCHSESSION AND A.CLASSLVL = D.CLASSLEVEL AND A.CLASSCODE = D.CLASSCODE
    left outer join tb_hse_common e
    on a.suid=e.suid and a.status=e.code_id and e.tb_id='STUDST'
    WHERE A.SCHYEAR = ?
    ORDER BY D.CLASSNAME, A.CLASSNO
     
    #1 Alan CKC, 2022-03-09
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    240
    讚:
    0
    你好,

    可以參考紅色的部份

    SELECT A.REGNO, A.SCHLVL, B.EN_DES, B.CH_DES, A.CLASSNO, D.CLASSNAME, A.SUBJGROUP, A.ENNAME, A.CHNAME, A.SEX, A.CCC,
    DATEFORMAT(A.DOB, 'DD/MM/YYYY') AS DOB, A.STRN, A.BIRTHCERT, A.HKID, A.SCHHOUSE,e.ch_des 'Status',
    dateformat(F.LASTATTEND,'DD/MM/YYYY') 'LASTATTEND_DATE'
    FROM wsadmin.VW_STU_LATESTSTUDENT A
    LEFT OUTER JOIN wsadmin.TB_HSE_COMMON B
    ON B.SUID = A.SUID AND B.TB_ID = 'SCHLVL' AND B.CODE_ID = A.SCHLVL
    LEFT OUTER JOIN wsadmin.TB_HSE_COMMON C
    ON C.SUID = A.SUID AND C.TB_ID = 'SESSIO' AND C.CODE_ID = A.SCHSESS
    LEFT OUTER JOIN wsadmin.TB_SCH_SCHCLASS D
    ON A.SUID = D.SUID AND A.SCHYEAR = D.SCHYEAR AND A.SCHLVL = D.SCHLEVEL AND A.SCHSESS = D.SCHSESSION AND A.CLASSLVL = D.CLASSLEVEL AND A.CLASSCODE = D.CLASSCODE
    left outer join wsadmin.tb_hse_common e
    on a.suid=e.suid and a.status=e.code_id and e.tb_id='STUDST'
    LEFT OUTER JOIN wsadmin.TB_STU_DEPARTURE F on A.suid=F.suid and A.stuid=F.stuid
    WHERE A.SCHYEAR = ?
    ORDER BY D.CLASSNAME, A.CLASSNO
     
    #2 edb-catherinewschan, 2022-03-10
  3. 57223717

    Alan CKC
    Expand Collapse

    文章:
    11
    讚:
    0
    THX
     
    #3 Alan CKC, 2022-03-10