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

想加埋 最後出席日期

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

 

你好,

可以參考紅色的部份

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

 

THX