Circ by Patrons Acting Outside Their Assigned Branch
This query looks at the checkout activity for patrons acting outside their assigned branches. In other words, a patron from Branch A goes and checks out from Branch B, that’d be counted in this query. We exclude the patron’s branch as the transacting branch and then see where their other checkouts are happening.
-- Let's work with a subset of data right from the start. Because there could be a *lot* of data involved.
-- Create a temp table to hold the specific check out transactions we need.
CREATE TABLE #TempOutsideCirc (
TransactionID INT
);
/* ------------------------------------------------------------------ */
-- Populate that temp table
INSERT INTO #TempOutsideCirc
SELECT
th.TransactionID
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get the patron's assigned branch
PolarisTransactions.Polaris.TransactionDetails pab WITH (NOLOCK)
ON (th.TransactionID = pab.TransactionID AND pab.TransactionSubTypeID = 123)
WHERE -- We don't want checkouts from this branch (see below). This number should match the one(s) below.
th.OrganizationID NOT IN (28)
AND -- We only want patrons from this branch, not the check outs. This number should match the one(s) above
pab.numValue = 28
AND -- Check outs
th.TransactionTypeID = 6001
AND -- Adjust your dates accordingly
th.TranClientDate BETWEEN '2022-04-01 00:00:00.000' AND '2022-04-30 23:59:59.999';
/* ------------------------------------------------------------------ */
SELECT
o.Name AS [Library / Branch],
mat.Description AS [Material Type],
COUNT(DISTINCT th.TransactionID) AS [Circ Count]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get the Material Type ID
PolarisTransactions.Polaris.TransactionDetails material WITH (NOLOCK)
ON (th.TransactionID = material.TransactionID AND material.TransactionSubTypeID = 4)
INNER JOIN -- Match the Material Type ID to a description
Polaris.Polaris.MaterialTypes mat WITH (NOLOCK)
ON (mat.MaterialTypeID = material.numValue)
INNER JOIN -- Get branch names
Polaris.Polaris.Organizations o WITH (NOLOCK)
ON (o.OrganizationID = th.OrganizationID)
WHERE -- Pull the data in from our temp table
th.TransactionID IN (SELECT TransactionID FROM #TempOutsideCirc)
GROUP BY
o.Name,
mat.[Description]
ORDER BY
o.Name,
mat.[Description]
-- Tidy up
DROP TABLE #TempOutsideCirc;