Active Patrons in Last 3 Years

Polaris
1

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.

2

Do you have access to simplyreports?

image
image2536×988 305 KB

3

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
2 Likes