Average time an item is in transit?

Polaris
1

Does anyone have a query or report for the average time an item is in transit? And is there a way to separate ones that are in transit to a specific branch based on an organization ID? i am wracking my brain to figure out how to do this one. Thanks

2

How long of a time span are you trying to cover?

You could piece together some information from the item history table depending on how long you keep that information. Doing that type of work is a bit tricky for sure, but our developer @mfields might have done something similar in the past.

If you don’t keep the item history table for long or want to cover a span longer than that, things get even trickier in the transactions database.

What are you hoping to change in your organization once you have this data?

3

I actually don’t know what it’s going to be for. It was a request from someone higher up. I don’t even know if they’re going to do anything specific with it.

4

It’s certainly possible. Are you looking for an average time in transit between locations or something more like an average time in-transit by material type/collection code? Or a bit of a blending of the two?

5

The former more than the latter. But material type would be good to know too. I’m sure i’ll be asked that at a future time if i can get the first kind of statistics.

6

So I started taking a look at this and had most of the code written when I had a sudden, dreadful thought. Things don’t get tricky in the Transactions database…

They don’t get there at all.

For reasons I cannot understand, In-Transit → In is not tracked by the Transactions database.

So you have to use the ItemRecordHistory and ItemRecordHistoryDaily tables in the primary Polaris database. Such a freakin’ faff of a thing to be forced into.

@adantes - Give me a bit to adapt my code. I meant to look at this yesterday but got pulled into a bunch of other things.

7

Okay, like I said, you’ll have to pull this from Polaris.Polaris.ItemRecordHistory to get the data you’re after. I think I commented everything to show what was doing what, where, and with which tables. But if you’ve got questions, let me know! The final data delivery gives you two results: The overall average number of days for all items transiting between the branches you’re interested in, and the average number of days in-transit for items by material type.

/* ---------- SETUP FRAMEWORK ---------- */

-- Create a table to hold the original check in data from our original check in branch
CREATE TABLE #OriginalCheckIns (
    ItemRecordID INT,
    TransasctionDate DATETIME
);

-- Create a table to hold the check in data from our target branch
CREATE TABLE #TargetCheckIns (
    ItemRecordID INT,
    TransactionDate DATETIME,
    DaysInTransit INT
);

-- Our original check in branch
DECLARE @CheckInOrg INT = 52;

-- Our target branch
DECLARE @TargetOrg INT = 51;

/* ---------- POPULATE TABLES ---------- */

-- Populate #OriginalCheckIns
INSERT INTO #OriginalCheckIns

SELECT
    ItemRecordID,
    TransactionDate

FROM
    Polaris.Polaris.ItemRecordHistory WITH (NOLOCK)

WHERE -- Check in
    ActionTakenID = 11
AND -- Item was previously checked out
    OldItemStatusID = 2
AND -- Item is now in-transit
    NewItemStatusID = 6
AND -- Item was checked in at our original library
    OrganizationID = @CheckInOrg
AND -- Item is in-transit to our target library
    InTransitRecvdBranchID = @TargetOrg
AND -- Adjust dates as needed
    TransactionDate BETWEEN '2023-05-01 00:00:00.000' AND '2023-05-31 23:59:59.999';

-- Debug line below
-- SELECT * FROM #OriginalCheckIns;

-- Populate #TargetCheckIns
INSERT INTO #TargetCheckIns

SELECT
    irh.ItemRecordID,
    irh.TransactionDate,
    DATEDIFF(DAY, oci.TransasctionDate, irh.TransactionDate) -- Math needed to get the number of days in-transit

FROM
    Polaris.Polaris.ItemRecordHistory irh WITH (NOLOCK)

INNER JOIN -- Keeping the data within our framework
    #OriginalCheckIns oci
    ON (oci.ItemRecordID = irh.ItemRecordID)

WHERE -- Automatic status change from Check In
    irh.ActionTakenID = 22
AND -- Item was previously in-transit
    irh.OldItemStatusID = 6
AND -- The item belongs at the target branch
    irh.AssignedBranchID = @TargetOrg
AND -- The item was checked in at the target branch
    irh.OrganizationID = @TargetOrg
AND -- We only care about the items tracked by #OriginalCheckIns
    irh.ItemRecordID IN (SELECT ItemRecordID FROM #OriginalCheckIns)
AND -- We're only interested in items that were checked in *after* their original check in date.
    oci.TransasctionDate < irh.TransactionDate;

-- Debug line below
-- SELECT * FROM #TargetCheckIns;

/* ---------- SWEET SWEET DATA DELIVERY ---------- */

-- Average number of days in-transit between branches
SELECT
    AVG(DaysInTransit) AS [Average Days in Transit]
FROM
    #TargetCheckIns

-- Average number of days in-transit by material type
SELECT
    mat.Description AS [Material Type],
    AVG(tci.DaysInTransit) AS [Avg Days in Transit]
FROM
    #TargetCheckIns tci
INNER JOIN
    Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
    ON (cir.ItemRecordID = tci.ItemRecordID)
INNER JOIN
    Polaris.Polaris.MaterialTypes mat WITH (NOLOCK)
    ON (mat.MaterialTypeID = cir.MaterialTypeID)
GROUP BY
    mat.Description
ORDER BY
    mat.Description

-- Tidy up
DROP TABLE #OriginalCheckIns;
DROP TABLE #TargetCheckIns;
3 Likes