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
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?
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.
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?
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.
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.
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;