I would like help calculating 'Overdue Duration', that is, the number of days an item was overdue for each overdue occurrence.

rbeverungen · May 31, 2023, 5:55pm

I had planned to subtract the due date from actual check-in date to return number of days the item was overdue, for each overdue occurrence. If an item is not yet returned,use current date. I can not find DueDate or an obvious way to derive DueDate. Any suggestions?

For context, this is part of an analysis of frequency and duration of overdue items. The goal is to count overdue frequency and duration, aggregating duration statistics, to provide ‘typical’ length of overdue periods for the library

CyberpunkLibrarian · June 1, 2023, 3:58pm

So to be clear here, do you want the overdue duration stats for a given item (or set of items)? Or are you looking more at something like the overdue duration over all overdue items?

rbeverungen · June 2, 2023, 12:18pm

Thanks for your reply, and sorry for any confusion in the original wording.

I would like to find, at row level, for all items that became overdue and were eventually returned: ItemRecordID, DueDate, OrganiztionID(of transacting branch), and Overdue Duration (defined as: number of elapsed days from due date to check in date).

My next approach is to see if I can derive Due Date by adding the appropriate number of days to the date that reminder emails are sent to patrons.

Thanks for considering this question.

CyberpunkLibrarian · June 2, 2023, 4:28pm

Okay, I think this will get you most of the way there. 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. I dropped out the minutes and seconds, but you can always unCAST them to get those back.

Let me know if anything looks weird or if you have questions:

-- 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,
    ItemRecordID INT,
    OrganizationID INT,
    PatronCodeID INT,
    LoanPeriodCodeID INT,
    LPID INT,
    LoanLength INT,
    CheckInDate DATETIME,
    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 a table of items checked in 
INSERT INTO #TempLoanedItems

SELECT
    th.TransactionID,
    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 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

/* ----------------- */

-- Deliver the data
SELECT
    DISTINCT tli.ItemRecordID,
    tli.OrganizationID,
    CAST(tli.CheckOutDate AS DATE) AS [Checkout Date],
    CAST(tli.CheckInDate AS DATE) AS [CheckInDate],
    tli.LoanLength - tlpm.LoanMinutes AS [Minutes Overdue],
    CAST((((CAST(tli.LoanLength AS DECIMAL(10,2)) - CAST(tlpm.LoanMinutes AS DECIMAL(10,2))) / 60) / 24) AS DECIMAL(7,2)) AS [Days Overdue]
FROM
    #TempLoanedItems tli
INNER JOIN
    Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
    ON (cir.ItemRecordID = tli.ItemRecordID)
INNER JOIN
    Polaris.Polaris.LoanPeriodCodes lpc WITH (NOLOCK)
    ON (lpc.LoanPeriodCodeID = cir.LoanPeriodCodeID)
INNER JOIN
    #TempLoanPeriodsMins tlpm
    ON (tli.LPID = tlpm.LPID)
AND
    tli.LoanLength - tlpm.LoanMinutes > 0

-- Tidy up
DROP TABLE #TempLoanedItems;
DROP TABLE #TempLoanPeriodsMins;

CyberpunkLibrarian · June 2, 2023, 4:31pm

Here’s a screenshot of what my results looked like.

rbeverungen · June 16, 2023, 3:17pm

@CyberpunkLibrarian , thank you for sharing this! These are exactly the results I was hoping for. I am looking at making the minor adjustments to customize the table, as you mentioned.

CyberpunkLibrarian · June 21, 2023, 8:23pm

No worries! But I’m going to go one better/further here. My boss pointed out to me that my original query doesn’t factor in auto-renewal activity which, honestly, I didn’t even think about.

So I rewrote and reconfigured the query to pull in a table of items that were auto-renewed which updates another table with updated checkout dates versus the original checkout dates. I also threw some quality of life stuff in there like titles, item barcodes, and patron info.

I’ll update the repo and Clearinghouse in just a few minutes.

-- 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 first target date
    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;