I am trying to produce a query that will automatically generate a list of absent students whenever a particular page is called. The name of this page is entitled "students_over_30_days.cfm". The problem I am running into is that the output list is displaying all of the dates that the students were in class including dates that are clearly under 30 days. My goal is to only list students that are over 30 days according to their most recent recorded date of attendance. Students that are not over 30 days will not be shown at all. It seems pretty simple enough however I'm running into a block. Is there a Syntax or two that I am missing?
<cfquery name="maxdate" datasource="master">
select name.fname, max(meedate) as lastclass from name left join meeting ON meefoiid = foiid
where 0=0 and meesquadlt = '1' group by meedate
</cfquery>
<cfset lastDate = #maxdate.lastclass# />
<!---<!DOCTYPE ......--->
<cfif DateDiff( "d", lastDate, Now() ) GTE 31>
<cfloop query="maxdate"><cfoutput> #maxdate.fname# is over 30 Days as of #maxdate.lastclass#<br /></cfoutput></cfloop>
<cfelse>
</cfif>
The results looks like this:
Karen is over 30 Days as of {ts '2013-10-01 00:00:00'}
Douglas (P) is over 30 Days as of {ts '2014-02-02 00:00:00'}
Tracy is over 30 Days as of {ts '2014-06-22 00:00:00'}
