0 Replies Latest reply on Nov 18, 2014 3:25 PM by HumbleCF

    over 30 day absent student list...

    HumbleCF

      I am trying to pull the names of all students who did not attend a class after 30 days. I usually do the MYsql syntax first before I write it for coldfusion. Each student's name in my meeting table is identified by an ID, studentID and meetingdate. Each studentID is also identified next to a date column showing that they were present. I would like to run a code that will identify all the students who's ID was null during the last 30 or so days as specified. I am able to run students who never came to class since the formation of the meeting table. However I am finding it difficult to specify the names of students who were not present (or null value) during a specific time span.

       

      /* This code below, which works just fine, will return a list of all of the students who never attended since the creation of the meeting table*/

      SELECT  trim(concat(name.fname,' ' ,name.Mname,' ',name.lname)) as student, name.noiid as Student_ID, sum(meeting.meestuid) as NO_CLASS, teamlt.ltfname as team

      FROM name

      LEFT JOIN meeting  ON name.stuid = meeting.meestuid

      LEFT JOIN teamlt  ON name.teamlt = teamlt.ltid

      WHERE  meeting.meestuid IS NULL

      AND name.Form4Complete ='yes'

      AND name.type = 'GOI'

      AND name.type <> 'jrgoi'

      AND name.city = 'orange county'

      GROUP BY student

      ORDER BY name.teamlt, student

       

       

      /* This code below will return an empty list. my goal is to produce a list of student who are  over 30days since their last class attendance*/

      SELECT  trim(concat(name.fname,' ' ,name.Mname,' ',name.lname)) as student, name.noiid as Student_ID, sum(meeting.meestuid) as NO_CLASS, teamlt.ltfname as team

      FROM name

      LEFT JOIN meeting  ON name.stuid = meeting.meestuid

      LEFT JOIN teamlt  ON name.teamlt = teamlt.ltid

      WHERE  meedate > '2014-10-17'

      AND meeting.meestuid IS NULL

      AND name.Form4Complete ='yes'

      AND name.type = 'GOI'

      AND name.type <> 'jrgoi'

      AND name.city = 'orange county'

      GROUP BY student

      ORDER BY name.teamlt, student

       

      /*i also tried the "WHERE  meedate between '2014-10-17'  and now() "*/

       

       

      it doesn't seem that difficult. If i can get this to run in MySQL i should be fine. Any Suggestions

      -JE