I’m hoping someone has a good SQL query to pull the active patron count by library with activity in the past 3 years. We want to run this monthly to review changes during the year. We don’t purge patron data, so we have patrons registered from 1999 that are no longer active.
When tracking active patron counts by library over the past three years, SQL is a more robust choice than SimplyReports. This is crucial considering your extensive patron data, some of which dates back to 1999. SimplyReports primarily uses “Last Activity Date,” which might overlook newer accounts without activity or older, inactive ones.
I recommend a SQL query with the
CASE function, combining Entry Date, Expiration Date, and Last Activity Date to accurately determine active statuses. This approach ensures:
- Entry Date is considered for new accounts.
- Expiration Date to filter out expired accounts, if desired.
- Last Activity Date to identify recent user engagement.
Given that some data may have been migrated over time, make sure your Entry Date field is complete and doesn’t contain null values. This is vital for the accuracy of new account tracking.
By coalescing or using a case statement with the Last Activity Date, Expiration Date, and Entry Date, you’ll get a true picture of active patrons. This method is ideal for a monthly review of patron activity changes.
DECLARE @ThreeYearsAgo DATE = DATEADD(year, -3, GETDATE()); SELECT -- Selecting the branch ID and name p.OrganizationID AS [Registered Branch ID], o.DisplayName AS [Registered Branch Name], -- Counting active patrons: Active if LastActivityDate or EntryDate is within the last 3 years and the account hasn't expired COUNT(CASE WHEN (ISNULL(p.LastActivityDate, pr.EntryDate) >= @ThreeYearsAgo) AND (ISNULL(pr.ExpirationDate, GETDATE()) >= GETDATE()) THEN 1 END) AS [Active Patrons (Last 3 Years)], -- Counting inactive patrons: Inactive if LastActivityDate and EntryDate are before the last 3 years or the account is expired COUNT(CASE WHEN (ISNULL(p.LastActivityDate, @ThreeYearsAgo) < @ThreeYearsAgo) OR (pr.ExpirationDate IS NOT NULL AND pr.ExpirationDate < GETDATE()) THEN 1 END) AS [Inactive Patrons (Before Last 3 Years or Expired)], -- Total count of patrons COUNT(*) AS [Total Patrons] FROM -- Joining necessary tables Polaris.Polaris.Patrons p WITH (NOLOCK) JOIN Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON pr.PatronID = p.PatronID JOIN Polaris.Polaris.Organizations o WITH (NOLOCK) ON p.OrganizationID = o.OrganizationID GROUP BY -- Grouping results by branch ID and name p.OrganizationID, o.DisplayName ORDER BY -- Ordering the results by branch ID for consistency p.OrganizationID