This will show titles a library doesn’t own, but patrons registered at that library have holds on.
declare @library int = 6 --parent (LIBRARY level) org id
declare @holdMin int = 2
select *
from (
select br.BibliographicRecordID,
MIN(br.browsetitle) as Title,
MIN(br.publicationyear) as PublicationYear,
min(br.browseauthor) as Author,
MIN(br.BrowseCallNo) as CallNumber,
MIN(bii.ISBNString) as ISBN,
MIN(mtom.description) as TypeOfMaterial,
COUNT(distinct cir.ItemRecordID) as Items,
COUNT(distinct shr.sysholdrequestid) as Holds
from polaris.polaris.BibliographicRecords br
join polaris.polaris.SysHoldRequests shr on
shr.BibliographicRecordID = br.BibliographicRecordID
join polaris.polaris.Patrons p on
shr.PatronID = p.PatronID
join polaris.polaris.Organizations o on
p.OrganizationID = o.OrganizationID
join polaris.polaris.CircItemRecords cir on
cir.AssociatedBibRecordID = br.BibliographicRecordID
join polaris.polaris.MARCTypeOfMaterial mtom on
br.PrimaryMARCTOMID = mtom.MARCTypeOfMaterialID
left join polaris.polaris.BibliographicISBNIndex bii on
bii.BibliographicRecordID = br.BibliographicRecordID
where
not exists
(
select 1
from polaris.polaris.CircItemRecords
join polaris.polaris.Organizations o on o.OrganizationID = AssignedBranchID
where br.BibliographicRecordID = AssociatedBibRecordID and o.ParentOrganizationID = @library and ItemStatusID in ( 1,2,3,4,5,6,12,13,14,15,17,18,19 )
)
and o.ParentOrganizationID = @library and shr.SysHoldStatusID in ( 1,3,4,5,6,9 )
group by br.BibliographicRecordID
) data
where Holds > @holdMin
order by Holds desc
option (recompile)