Specific example : SQL report that would track the age of the card holder (Patron Code?) checking out items from the YA DVD collection?
You could give this a try. Adapted from a Discord post by @mhammermeister and tweaked with chatgpt (hence the weird formatting). This will show all collection codes and all patron codes for the time period specified. It also shows first time CKOs as well as renewals. If the patron record was deleted during the time period, then those circs will NOT be counted since there is no patron record to pull the patroncodeid from.
If you see any NULLs listed, then that means the items CKO’d did not have a Collection Code, since it is not a required field in Polaris on the item record.
This may take awhile to run since it uses the transactions database.
SELECT
pc.Description AS PatronCodeDescription,
c.Name AS CollectionName,
COUNT(DISTINCT td_p.numValue) AS Patrons,
COUNT(DISTINCT th.TransactionID) AS Circs
FROM
PolarisTransactions.Polaris.TransactionHeaders th
JOIN
PolarisTransactions.Polaris.TransactionDetails td_p
ON th.TransactionID = td_p.TransactionID
AND td_p.TransactionSubTypeID = 6
JOIN
PolarisTransactions.Polaris.TransactionDetails td_c
ON th.TransactionID = td_c.TransactionID
AND td_c.TransactionSubTypeID = 61
LEFT JOIN
Polaris.Collections c
ON c.CollectionID = td_c.numValue
JOIN
Polaris.Patrons p
ON p.PatronID = td_p.numValue
JOIN
Polaris.PatronCodes pc
ON pc.PatronCodeID = p.PatronCodeID
WHERE
th.TransactionTypeID = 6001
AND th.TranClientDate BETWEEN '2023-01-01' AND '2024-01-01' -- change
GROUP BY
pc.Description,
c.Name
ORDER BY
c.Name,
pc.Description;
Sample output:
1 Like