Patron Circ Activity by City
This query will pull patron check out activity at a given location and provide you with a count of patrons in cities, states, and countries. At most, this is really targeted at getting a count of patrons checking out items and grouping the count by city, but since there are lots of cities with the same names, I threw in states and countries to make sure you get unambiguous results. I marked this as a patron report rather than a circ report because, in the end, we’re counting patrons, not circs.
-- Let's create a subset of specific checkout data, otherwise we'll be here all day.
-- Create a temp table to hold circ data specific to a location.
CREATE TABLE #TempPatronCircTrans (
TransactionID INT
);
/* ------------------------------------------------------------------ */
-- Populate that temp table
INSERT INTO #TempPatronCircTrans
SELECT
TransactionID
FROM
PolarisTransactions.Polaris.TransactionHeaders WITH (NOLOCK)
WHERE -- Checkouts
TransactionTypeID = 6001
AND -- Drop in the OrganizationID for your branch/library
OrganizationID = 28
AND -- Adjust dates accordingly
TranClientDate BETWEEN '2022-04-01 00:00:00.000' AND '2023-03-31 23:59:59.999';
/* ------------------------------------------------------------------ */
-- Now, let's get more data based on that subset
SELECT
pc.City AS [City],
pc.State AS [State],
ct.Country AS [Country],
COUNT(DISTINCT patron.numValue) AS [Patrons] -- Patrons are counted once and only once
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get the PatronID
PolarisTransactions.Polaris.TransactionDetails patron WITH (NOLOCK)
ON (th.TransactionID = patron.TransactionID AND patron.TransactionSubTypeID = 6)
INNER JOIN -- Get the PostalCodeID
PolarisTransactions.Polaris.TransactionDetails zipcode WITH (NOLOCK)
ON (th.TransactionID = zipcode.TransactionID AND zipcode.TransactionSubTypeID = 302)
INNER JOIN
Polaris.Polaris.PostalCodes pc WITH (NOLOCK)
ON (pc.PostalCodeID = zipcode.numValue)
INNER JOIN
Polaris.Polaris.Countries ct WITH (NOLOCK)
ON (ct.CountryID = pc.CountryID)
WHERE
th.TransactionID IN (SELECT TransactionID FROM #TempPatronCircTrans)
GROUP BY
pc.City,
pc.State,
ct.Country
ORDER BY
[Patrons] DESC
DROP TABLE #TempPatronCircTrans;