SQL timestamp problem in sql

本文由 ptam 在 2020-09-16 發表於 "WebSAMS 討論區" 討論區

  1. 55708113

    ptam
    Expand Collapse

    文章:
    97
    讚:
    0
    Writing a sql not to include students who were early departed. Error found in this line:

    datediff(month, 'last attended day', current date) < 3

    How to solve it? Thanks.

    Select b.CLASSLVL 'Class Level', b.CLASSCODE 'Class Code', b.CLASSNO 'Class No', b.regno, b.ENNAME 'Name', c1.en_des 'Subject', c.SYSSCORE 'Subject Score', c.OMCLASSLVL 'Subject Rank', a.SYSPERCSCORE 'Avg Score', a.OMCLASSLVL 'Avg Rank', (select dateformat(a1.LASTATTEND,'DD/MM/YYYY') from TB_STU_DEPARTURE a1 WHERE a.suid=a1.suid and a.stuid=a1.stuid) 'last attended day' from wsadmin.TB_ASR_STUDASSESSDATA a join wsadmin.VW_STU_LATESTSTUDENT b on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = b.SCHYEAR and a.SCHYEAR = 2019 and a.TIMESEQ = 1300 LEFT OUTER JOIN wsadmin.TB_ASR_SUBJASSESSDATA c on b.SUID = c.SUID and b.SCHSESS = c.SCHSESSION and b.SCHLVL = c.SCHLEVEL and b.STUID = c.STUID and b.CLASSLVL = c.CLASSLEVEL and b.SCHYEAR = c.SCHYEAR and a.TIMESEQ = c.TIMESEQ LEFT OUTER JOIN TB_HSE_COMMON c1 ON c.SUID=c1.SUID AND c1.CODE_ID=c.SUBJCODE AND c1.TB_ID='SBJ' left outer join wsadmin.TB_STU_STUSUBJ e on b.suid=e.suid and b.stuid=e.stuid and c.SUBJCODE = e.SUBJCODE and b.STUSCHRECID=e.STUSCHRECID where b.CLASSLVL ='S4' and c.OMCLASSLVL<4 and datediff(month, 'last attended day', current date) < 3 order by b.SCHSESS, b.CLASSLVL, c.SUBJCODE, c.OMCLASSLVL
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    191
    讚:
    0
    Hi,

    Please try to correct 2 parts as below:-

    Remove the dateformat function:

    a.OMCLASSLVL 'Avg Rank',
    (select LASTATTEND from wsadmin.TB_STU_DEPARTURE a1
    WHERE a.suid=a1.suid and a.stuid=a1.stuid) as last_attended_day

    Replace the field name as last_attended_day:

    where b.CLASSLVL ='S4' and c.OMCLASSLVL<4
    and datediff(month, last_attended_day , current date) < 3

    order by b.SCHSESS, b.CLASSLVL, c.SUBJCODE, c.OMCLASSLVL

     
    #2 edb-catherinewschan, 2020-09-17
  3. 55708113

    ptam
    Expand Collapse

    文章:
    97
    讚:
    0
    Hi Catherine, It works. Thanks.