Absentee Report

[rakess70]

I am looking to create an absentee report for those who have not attended any event in the last x number of days. This looks like it could be a fairly complicated query. Has anyone else figured something like this out yet so I don't have to recreate the wheel?

[rakess70]

OK, I figured out the Query for our database. I would love some help figuring out how to make this a query listing.

[rakess70]

Shows Every (Guest, Member, Regular Attender, Occasional Attender) who have not attended any event between a given date range

I would think it would be good to have a selector for the classifications you want to include and 2 date pickers to set the range.

SELECT person.per_ID, person.per_FirstName, person.per_LastName, person.Per_Email, family.fam_Address1, family.fam_Address2, family.fam_City, family.fam_State, family.fam_Zip, person.per_FriendDate, person.per_HomePhone, person.per_CellPhone, class.lst_OptionName FROM person_per person LEFT JOIN ( SELECT DISTINCT person.per_id FROM event_attend attend JOIN events_event event ON event.event_id = attend.event_id JOIN person_per person ON attend.person_id = person.per_id WHERE event.event_start BETWEEN '2017-01-01' AND '2017-02-06' ) as attendees ON attendees.per_id = person.per_id JOIN family_fam family on person.per_fam_ID = family.fam_ID INNER JOIN list_lst class on person.per_cls_id = class.lst_OptionID WHERE attendees.per_id IS NULL AND class.lst_id = 1 AND person.per_cls_id IN (1,2,3,6) ORDER BY class.lst_OptionName, person.per_LastName, person.per_FirstName;

[rakess70]

Another report I wrote is an individual attendance list for a person

SELECT event.event_title, event.event_start FROM event_attend attend JOIN events_event event ON event.event_id = attend.event_id WHERE attend.person_id = 176 ORDER BY event.event_start DESC

[rakess70]

@crossan007 any word on how I can help in getting these in the report menu?

[crossan007]

@rakess70 you should be able to get them in the Reports | Queries menu fairly easily by adding a row to the query_qry table.

Reports | Reports would be a little more difficult, as you'd have to create a whole new page for each one.

That said - I think we have a lot of room to grow with how these are managed. I don't have a clear picture of how it'll be implemented.

[rakess70]

I never did get this plugged in to system and I have been out of the loop for awhile any chance someone might want to help me get these plugged in?

[github-actions[bot]]

This issue is stale because it has been open 30 days with no activity. Remove stale label or comment or this will be closed in 5 days.

[github-actions[bot]]

This issue is stale because it has been open 30 days with no activity. Remove stale label or comment or this will be closed in 5 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 15 days with no activity.