Absents Report

This script will display a list of everyone who was a Member of any of the Organizations that match the filters on the Organization Search/ Manage page and who was absent at the last scheduled meeting. When you run the report for the first time from Special Content, you can choose to Add to Menu. If you select that option, it will add the new report to the blue Toolbar on the Organization Search page. The report will display under the code icon in the Custom SQL Reports section.

The following information is contained on the report (which is also available to be downloaded to Excel):

  • Name (link to the people record)
  • Age
  • Family Position
  • Home Phone
  • Cell Phone
  • Email Address
  • Absents (number)
  • Last Attended Date and Time
  • Organization (link to the org)

Note

The script below is using the sort order of Organization, number of absences, and then name. You can revise it to suit your needs.

Sample Report

Notice the sample below. The names of the people and the organizations are links. On your report, to the left of the data, there will be a button to Download as Excel

https://i.tpsdb.com/./2018-03-12_17-12-03.png

Create to SQL Report

Follow the instructions in this help document to create the report. See How to create a SQL Script, using the code below for the script. Name the report AbsentsReport.

Note

Be sure that run the report after saving it and then click Add to Menu if you want it available on the blue Toolbar on the Organization > Search/Manage page. The first time you run the report from Special Content, there will be no data. You run it here so that you can make it available on the menu/toolbar.

SQL Code

WITH LastAttend AS
(
  SELECT a.OrganizationId, a.PeopleId, MAX(MeetingDate) LastMeeting
  FROM Attend a
  JOIN dbo.OrganizationMembers om ON om.OrganizationId = a.OrganizationId AND om.PeopleId = a.PeopleId
  WHERE EXISTS(SELECT NULL FROM dbo.SplitInts(@orgids) WHERE Value = a.OrganizationId)
  AND AttendanceFlag = 1
  AND om.MemberTypeId NOT IN (230, 311, 415, 500)
  GROUP BY a.OrganizationId, a.PeopleId
),
Consecutive AS (
    SELECT a.OrganizationId, a.PeopleId, COUNT(*) AS consecutive, la.LastMeeting lastattend
    FROM dbo.Attend a
    JOIN LastAttend la ON la.OrganizationId = a.OrganizationId AND la.PeopleId = a.PeopleId
        AND a.MeetingDate > la.LastMeeting
    GROUP BY a.OrganizationId, a.PeopleId, la.LastMeeting
)
SELECT
    '/Person2/' + convert(varchar, p.PeopleId) LinkForNext
    ,p.Name
    ,p.Age
    ,fp.Description FamilyPosition
    ,dbo.FmtPhone(p.HomePhone) Home
    ,dbo.FmtPhone(p.CellPhone) Cell
    ,p.EmailAddress
    ,c.consecutive Absents
    ,c.lastattend LastAttendDt
    ,'/Org/' + convert(varchar, o.OrganizationId) LinkForNext
    ,o.OrganizationName Organization
    --,p.FamilyId
FROM Consecutive c
JOIN dbo.People p ON p.PeopleId = c.PeopleId
JOIN dbo.Organizations o ON o.OrganizationId = c.OrganizationId
JOIN lookup.FamilyPosition fp ON fp.Id = p.PositionInFamilyId
ORDER BY OrganizationName, Absents, Name2


Latest Update 11/13/2020

Modify image link with secure protocol.

© 2020 TouchPoint Software, LLC.  All rights reserved.