Requested titles your library doesn't own

Clearinghouse Repository Polaris Repository
1

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)