This query pulls a list of items that were checked in overdue and reports each item’s overdue duration in days. You’ll likely need to adjust this to suit your own needs and, as a big warning, this query is geared to work with items set to have loan periods measured in Days. If you’re going to look at items with loan periods measured in Minutes or Hours, you’ll definitely need to tweak the settings.
The overdue duration is based upon a TransactionSubType within a 6002 TransactionType, which is check in. There’s a SubType in there that defines the loan period in minutes, so there’s a bit of maths going from minutes to days. The check out date is derived from subtracting the number of minutes from the check in date.
The original query didn’t factor in auto-renewal activity, so I rewrote and reconfigured to account for that. One thing to note: This query doesn’t take closed dates into account so your day calculations may be slightly off because of that. There area a few data points in this query that aren’t fully utilized but I left them there in case they might be useful to someone else later on. From what I can tell, the execution speed doesn’t suffer from their presence.
I dropped out the minutes and seconds, but you can always unCAST them to get those back.
-- Set a variable for our OrganizationID
DECLARE @OrgID INT = 5;
-- Create a table to hold Loan Period information
CREATE TABLE #TempLoanPeriodsMins (
LPID INT,
OrganizationID INT,
PatronCodeID INT,
LoanPeriodCodeID INT,
[Description] NVARCHAR(80),
TimeUnit INT,
Units INT,
LoanMinutes INT
)
-- Create a table to hold data about items checked in
CREATE TABLE #TempLoanedItems (
TransactionID INT,
PatronID INT,
ItemRecordID INT,
OrganizationID INT,
PatronCodeID INT,
LoanPeriodCodeID INT,
LPID INT,
LoanLength INT,
CheckInDate DATETIME,
CheckOutDate DATETIME
)
-- Create a table to hold data about all auto-renwals
CREATE TABlE #TempAutoRenewalsSetup (
TransactionID INT,
ItemRecordID INT,
PatronID INT,
CheckoutDate DATETIME
)
-- Create a table to hold data about the latest auto-renewals
CREATE TABLE #TempAutoRenewals (
TransactionID INT,
ItemRecordID INT,
PatronID INT,
CheckoutDate DATETIME
)
/* ----------------- */
-- Populate the Loan Period table
INSERT INTO #TempLoanPeriodsMins
SELECT
lp.LPID,
lp.OrganizationID,
lp.PatronCodeID,
lp.LoanPeriodCodeID,
lpc.Description,
lp.TimeUnit,
lp.Units,
((60 * 24) * lp.Units) AS LoanMinutes -- Converts the days to minutes
FROM
Polaris.Polaris.LoanPeriods lp WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.LoanPeriodCodes lpc WITH (NOLOCK)
ON (lpc.LoanPeriodCodeID = lp.LoanPeriodCodeID)
WHERE --TimeUnit 1 = Days
lp.TimeUnit = 1
AND
lp.OrganizationID = @OrgID;
/* ----------------- */
-- Populate the #TempAutoRenewals table
INSERT INTO #TempAutoRenewalsSetup
SELECT
th.TransactionID,
item.numValue,
patron.numValue,
checkout.dateValue
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get the ItemRecordID
PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
ON (th.TransactionID = item.TransactionID AND item.TransactionSubTypeID = 38)
INNER JOIN -- Get the PatronID
PolarisTransactions.Polaris.TransactionDetails patron WITH (NOLOCK)
ON (th.TransactionID = patron.TransactionID AND patron.TransactionSubTypeID = 6)
INNER JOIN -- Get the checkout date
PolarisTransactions.Polaris.TransactionDetails checkout WITH (NOLOCK)
ON (th.TransactionID = checkout.TransactionID AND checkout.TransactionSubTypeID = 186)
INNER JOIN -- Get the auto-renewal status
PolarisTransactions.Polaris.TransactionDetails autorenew WITH (NOLOCK)
ON (th.TransactionID = autorenew.TransactionID AND autorenew.TransactionSubTypeID = 145)
WHERE -- Check out
th.TransactionTypeID = 6001
AND -- Item was auto-renewed
autorenew.numValue = 48
AND -- Set your first date to be at least two months back from your target dates
th.TranClientDate BETWEEN '2023-03-01 00:00:00.000' AND '2023-05-31 23:59:59.999'
/* ----------------- */
-- Populate the table of most recent auto-renewals
INSERT INTO #TempAutoRenewals
SELECT
TransactionID,
ItemRecordID,
PatronID,
MAX(CheckoutDate) -- Use only the latest auto-renewal checkout date
FROM
#TempAutoRenewalsSetup
GROUP BY
TransactionID,
ItemRecordID,
PatronID
-- Populate a table of items checked in
INSERT INTO #TempLoanedItems
SELECT
th.TransactionID,
patron.numValue,
item.numValue,
th.OrganizationID,
patroncode.numValue,
cir.LoanPeriodCodeID,
(SELECT LPID FROM Polaris.Polaris.LoanPeriods lp WITH (NOLOCK)
WHERE lp.LoanPeriodCodeID = cir.LoanPeriodCodeID
AND lp.OrganizationID = th.OrganizationID
AND lp.PatronCodeID = patroncode.numValue) AS LPID,
loanlength.numValue,
checkindate.dateValue,
DATEADD(MINUTE, -loanlength.numValue, checkindate.dateValue) AS CheckOutDate
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Pulls PatronID
PolarisTransactions.Polaris.TransactionDetails patron WITH (NOLOCK)
ON (th.TransactionID = patron.TransactionID AND patron.TransactionSubTypeID = 6)
INNER JOIN -- Pulls ItemRecordID
PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
ON (th.TransactionID = item.TransactionID AND item.TransactionSubTypeID = 38)
INNER JOIN -- Need the LoanPeriodCodeID
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
ON (cir.ItemRecordID = item.numValue)
INNER JOIN -- Pulls the loan length in minutes
PolarisTransactions.Polaris.TransactionDetails loanlength WITH (NOLOCK)
ON (th.TransactionID = loanlength.TransactionID AND loanlength.TransactionSubTypeID = 323)
INNER JOIN -- Gets the patron code
PolarisTransactions.Polaris.TransactionDetails patroncode WITH (NOLOCK)
ON (th.TransactionID = patroncode.TransactionID AND patroncode.TransactionSubTypeID = 7)
INNER JOIN -- Gets the date the item was checked in
PolarisTransactions.Polaris.TransactionDetails checkindate WITH (NOLOCK)
ON (th.TransactionID = checkindate.TransactionID AND checkindate.TransactionSubTypeID = 185)
WHERE -- Check in
th.TransactionTypeID = 6002
AND -- Adjust dates as needed
th.TranClientDate BETWEEN '2023-05-01 00:00:00.000' AND '2023-05-31 23:59:59.999'
AND
th.OrganizationID = @OrgID
AND -- Excludes oddities like circ-on-the-fly etc.
loanlength.numvalue > 0;
-- Update #TempLoanedItems to reflect renewal dates from #TempAutoRenewals
UPDATE
#TempLoanedItems
SET
#TempLoanedItems.CheckOutDate = #TempAutoRenewals.CheckoutDate
FROM
#TempAutoRenewals
WHERE
#TempLoanedItems.PatronID = #TempAutoRenewals.PatronID
AND
#TempLoanedItems.ItemRecordID = #TempAutoRenewals.ItemRecordID
AND
#TempAutoRenewals.CheckoutDate > #TempLoanedItems.CheckOutDate;
-- Deliver the data
SELECT
tli.ItemRecordID,
cir.Barcode AS [Item Barcode],
br.BrowseTitle AS [Title],
tli.PatronID,
p.Barcode AS [Patron Barcode],
pr.PatronFullName AS [Patron Name],
CAST(tli.CheckoutDate AS DATE) AS [Check Out Date],
CAST(tli.CheckInDate AS DATE) AS [Check In Date],
DATEDIFF(DAY, tli.CheckOutDate, tli.CheckinDate) AS [Days Out],
lp.Units [Item Loan Period in Days],
(DATEDIFF(DAY, tli.CheckOutDate, tli.CheckinDate) - lp.Units) AS [Days Overdue]
FROM
#TempLoanedItems tli
INNER JOIN -- Get Loan Period info
Polaris.Polaris.LoanPeriods lp WITH (NOLOCK)
ON (lp.LPID = tli.LPID)
INNER JOIN -- Get item record info
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
ON (cir.ItemRecordID = tli.ItemRecordID)
INNER JOIN -- Get title info
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK)
ON (br.BibliographicRecordID = cir.AssociatedBibRecordID)
INNER JOIN -- Get patron registration info
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
ON (pr.PatronID = tli.PatronID)
INNER JOIN -- Get paton barcode
Polaris.Polaris.Patrons p WITH (NOLOCK)
ON (p.PatronID = tli.PatronID)
WHERE
(DATEDIFF(DAY, tli.CheckOutDate, tli.CheckinDate) - lp.Units) > 0
ORDER BY
(DATEDIFF(DAY, tli.CheckOutDate, tli.CheckinDate) - lp.Units) DESC
-- Tidy up
DROP TABLE #TempLoanedItems;
DROP TABLE #TempLoanPeriodsMins;
DROP TABLE #TempAutoRenewalsSetup;
DROP TABLE #TempAutoRenewals;