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.
SELECT<br></br> cir.Barcode,<br></br> br.BrowseTitle,<br></br> ird.CallNumber,<br></br> o.DisplayName AS [CKI Location],<br></br> ab.DisplayName AS [Assigned Location]<br></br>FROM<br></br> ItemRecordHistory irh (nolock)<br></br><br></br>JOIN<br></br> CircItemRecords cir (nolock) on irh.ItemRecordID = cir.ItemRecordID<br></br>JOIN<br></br> ItemRecordDetails ird (nolock) on irh.ItemRecordID = ird.ItemRecordID<br></br>JOIN<br></br> BibliographicRecords br (nolock) on cir.AssociatedBibRecordID = br.BibliographicRecordID<br></br>JOIN<br></br> Organizations o (nolock) on irh.OrganizationID = o.OrganizationID<br></br>join<br></br> Organizations ab (nolock) on cir.AssignedBranchID = ab.OrganizationID<br></br><br></br>WHERE<br></br> irh.ActionTakenID = 11<br></br>AND<br></br> irh.OrganizationID <> irh.AssignedBranchID<br></br>AND<br></br> irh.OrganizationID <> cir.AssignedBranchID<br></br>AND<br></br> cir.ItemStatusID = 1<br></br>AND<br></br> irh.TransactionDate = cir.ItemStatusDate<br></br>AND<br></br> cir.ItemStatusDate = cir.CheckInDate<br></br>AND<br></br> cir.Barcode not like 'econtent%'<br></br>AND<br></br> irh.OrganizationID not in (19,22,17,20)<br></br>AND<br></br> irh.ItemRecordID not in (<br></br> SELECT DISTINCT<br></br> cir.ItemRecordID <br></br> FROM<br></br> ItemRecordHistory irh (nolock)<br></br> JOIN<br></br> CircItemRecords cir (nolock) on irh.ItemRecordID = cir.ItemRecordID<br></br> WHERE<br></br> irh.ActionTakenID = 27<br></br> AND<br></br> irh.OrganizationID = irh.AssignedBranchID<br></br> AND<br></br> irh.OrganizationID <> cir.AssignedBranchID<br></br> AND<br></br> cir.ItemStatusID = 1<br></br> AND<br></br> irh.TransactionDate > cir.ItemStatusDate<br></br> AND<br></br> cir.ItemStatusDate = cir.CheckInDate<br></br> AND<br></br> cir.Barcode not like 'econtent%'<br></br> )