over 30 day absent student list...

New Here ,
Nov 18, 2014 Nov 18, 2014

Copy link to clipboard

Copied

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

Views

295

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
no replies

Have something to add?

Join the conversation