Find Tool SQL - Search By Lexile Range

Clearinghouse Repository Polaris Repository
1

One of my libraries asked for a search to find items in a particular Lexile range. I couldn’t find anything to steal from, so I figured I would share what I put together.

This search relies on data being present in the 521$a with a “$b Lexile” It ignores all of the Lexile “prefix codes” and the converts the entire string to a number. The one exception is “BR” which acts as a negative. “BR100” should be interpreted in this query as “-100”.

Select distinct A.BibliographicRecordID
From (
	Select  BR.BibliographicRecordID,
		try_cast(trim(replace(replace(replace(replace(replace(replace(replace(replace(BSa.Data,'L',''),'AD',''),'NC',''),'H',''),'IG',''),'GN',''),'BR','-'),'NP','')) as int) AS Lexile
	From Polaris.BibliographicREcords BR 
	JOIN Polaris.BibliographicTags BT
		ON BR.BibliographicRecordID = Bt.BibliographicRecordID
		AND BT.TagNumber = 521
	JOIN Polaris.BibliographicSubfields BSa
		ON BT.BibliographicTagID = BSa.BibliographicTagID
		AND BSa.Subfield = 'a'
	JOIN Polaris.BibliographicSubfields BSb
		ON BT.BibliographicTagID = BSb.BibliographicTagID
		AND BSb.Subfield = 'b'
		AND BSb.Data LIKE '%LEXILE%'
	JOIN Polaris.CircItemRecords CIR
		ON CIR.AssociatedBibRecordID = BR.BibliographicRecordID
		AND CIR.RecordStatusID = 1
		AND CIR.AssignedBranchID IN (15,16,17)
	) 
	AS A
WHERE A.Lexile between 400 and 495

Staff can copy/paste this to a Bib Find Tool.

  • Change the last line to the correct lexile range
  • Change the 4th line from the bottom - “AssignedBranchID IN (…)” or remove it altogether.
1 Like