Find Items Denied Transit Without Branch Update

Clearinghouse Repository Polaris Repository
,
1

Find Items Denied Transit Without Branch Update

Submitted by Gabrielle Gosselin at Richland Library (Columbia, SC) - Thank you!

The goal: find items where staff denied transit at CKI but didn’t update the assigned branch to accurately reflect where the item physically was. The report the query will go in is intended for staff to run, sort by CKI Location column, find the ones listed for their branch, and go look on the shelves for them and if found, fix the item record.

WITH AvailableItems AS (
  SELECT cir.Barcode,
         br.BrowseTitle,
         ird.CallNumber,
         o.DisplayName AS CKI_Location,
         ab.DisplayName AS Assigned_Location
  FROM CircItemRecords cir
  JOIN ItemRecordDetails ird ON cir.ItemRecordID = ird.ItemRecordID
  JOIN BibliographicRecords br ON cir.AssociatedBibRecordID = br.BibliographicRecordID
  JOIN Organizations o ON cir.OrganizationID = o.OrganizationID
  JOIN Organizations ab ON cir.AssignedBranchID = ab.OrganizationID
  WHERE cir.ItemStatusID = 1
    AND cir.Barcode NOT LIKE 'econtent%'
)
SELECT *
FROM AvailableItems
JOIN ItemRecordHistory irh ON AvailableItems.ItemRecordID = irh.ItemRecordID
WHERE irh.ActionTakenID = 11
  AND irh.OrganizationID <> irh.AssignedBranchID
  AND irh.OrganizationID <> AvailableItems.Assigned_BranchID
  AND irh.OrganizationID NOT IN (19, 22, 17, 20)
  AND irh.TransactionDate = AvailableItems.ItemStatusDate
  AND NOT EXISTS (
    SELECT 1
    FROM ItemRecordHistory irh2
    WHERE irh2.ItemRecordID = AvailableItems.ItemRecordID
      AND irh2.ActionTakenID = 27
      AND irh2.OrganizationID = irh2.AssignedBranchID
      AND irh2.OrganizationID <> AvailableItems.Assigned_BranchID
      AND irh2.TransactionDate > AvailableItems.ItemStatusDate
  );