Top titles by material type sql issue

Polaris
1

So I cribbed this SQL to find top titles and does appear to be working but I can not figure out why the reports circ count doesn’t always match Polaris. Could it be unique patrons or no renewals? If anyone has suggests, it would be much appreciated :slight_smile:

Here is my code:

SELECT TOP 40 – Pulls the top 40 titles, adjust as needed
br.BibliographicRecordID AS [BibliographicRecordID],
br.BrowseTitle AS [Title],
br.BrowseAuthor AS [Author],
material.numValue AS [MaterialTypeID],
mat.Description AS [Material Type],
shelfloc.numValue AS [ShelfLocationID],
sl.Description AS [Shelf Location],
COUNT(shelfloc.numValue) AS [Circ Count]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN – Get the MaterialTypeID
PolarisTransactions.Polaris.TransactionDetails material WITH (NOLOCK)
ON (th.TransactionID = material.TransactionID AND material.TransactionSubTypeID = 4)
LEFT JOIN – Get the ShelfLocationID if it’s there
PolarisTransactions.Polaris.TransactionDetails shelfloc WITH (NOLOCK)
ON (th.TransactionID = shelfloc.TransactionID AND shelfloc.TransactionSubTypeID = 296)
INNER JOIN – Get the ItemRecordID
PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
ON (th.TransactionID = item.TransactionID AND item.TransactionSubTypeID = 38)
LEFT JOIN – Get item record information
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
ON (cir.ItemRecordID = item.numValue)
LEFT JOIN – Get bibliographic record information
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK)
ON (br.BibliographicRecordID = cir.AssociatedBibRecordID)
INNER JOIN – Get material type information
Polaris.Polaris.MaterialTypes mat WITH (NOLOCK)
ON (mat.MaterialTypeID = material.numValue)
LEFT JOIN – Get shelf location information
Polaris.Polaris.ShelfLocations sl WITH (NOLOCK)
ON (sl.ShelfLocationID = shelfloc.numValue)
WHERE – Check outs
th.TransactionTypeID = 6001
AND – Adjust dates as needed
th.TranClientDate BETWEEN ‘2025-07-01 00:00:00.000’ AND ‘2026-06-23 23:59:59.999’
AND – Material type
mat.MaterialTypeID = 110
AND – Exclude deleted bibs
br.BibliographicRecordID IS NOT NULL
GROUP BY
br.BibliographicRecordID,
br.BrowseTitle,
br.BrowseAuthor,
material.numValue,
mat.Description,
shelfloc.numValue,
sl.Description
ORDER BY
COUNT(shelfloc.numValue) DESC

2

I initially suspected there was some sort of JOIN related issue, but didn’t catch it on my initial read… but if we display the output without grouping it for the count, we can clearly see that’s what’s going on:

image
image1072×408 54.6 KB

So, it was just a matter of identifying the source of the problem… and of course it’s near the end of the query (isn’t it always?). It’s your LEFT JOIN for the Shelf Description. The ShelfLocationIDs are repeated for every OrganizationID in that uses them, so you need to get the Item Assigned Branch ID from the TransactionDetails and add that to your ON statement for the JOIN:

LEFT JOIN --Get Item AssignedBranchID from TransactionDetails
PolarisTransactions.Polaris.TransactionDetails ab WITH (NOLOCK)
ON (th.TransactionID = ab.TransactionID AND
ab.TransactionSubTypeID = 125)

LEFT JOIN --Get shelf location information
Polaris.Polaris.ShelfLocations sl WITH (NOLOCK)
ON (sl.ShelfLocationID = shelfloc.numValue AND sl.OrganizationID = ab.numValue)

The complete query with those additions would look like this:

SELECT TOP 40 -- Pulls the top 40 titles, adjust as needed
br.BibliographicRecordID AS [BibliographicRecordID],
br.BrowseTitle AS [Title],
br.BrowseAuthor AS [Author],
material.numValue AS [MaterialTypeID],
mat.Description AS [Material Type],
shelfloc.numValue AS [ShelfLocationID],
sl.Description AS [Shelf Location],
COUNT(shelfloc.numValue) AS [Circ Count]

FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)

INNER JOIN -- Get the MaterialTypeID
PolarisTransactions.Polaris.TransactionDetails material WITH (NOLOCK)
ON (th.TransactionID = material.TransactionID AND material.TransactionSubTypeID = 4)

LEFT JOIN -- Get the ShelfLocationID if it’s there
PolarisTransactions.Polaris.TransactionDetails shelfloc WITH (NOLOCK)
ON (th.TransactionID = shelfloc.TransactionID AND shelfloc.TransactionSubTypeID = 296)

LEFT JOIN --Get Item AssignedBranchID from TransactionDetails
PolarisTransactions.Polaris.TransactionDetails ab WITH (NOLOCK)
ON (th.TransactionID = ab.TransactionID AND
ab.TransactionSubTypeID = 125)

INNER JOIN -- Get the ItemRecordID
PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
ON (th.TransactionID = item.TransactionID AND item.TransactionSubTypeID = 38)

LEFT JOIN -- Get item record information
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
ON (cir.ItemRecordID = item.numValue)

LEFT JOIN -- Get bibliographic record information
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK)
ON (br.BibliographicRecordID = cir.AssociatedBibRecordID)

INNER JOIN -- Get material type information
Polaris.Polaris.MaterialTypes mat WITH (NOLOCK)
ON (mat.MaterialTypeID = material.numValue)

LEFT JOIN -- Get shelf location information
Polaris.Polaris.ShelfLocations sl WITH (NOLOCK)
ON (sl.ShelfLocationID = shelfloc.numValue AND sl.OrganizationID = ab.numValue)

WHERE -- Check outs
	th.TransactionTypeID = 6001
AND -- Adjust dates as needed
	th.TranClientDate BETWEEN '2025-07-01 00:00:00.000' AND '2026-06-23 23:59:59.999'
AND -- Material type
	mat.MaterialTypeID = 110
AND -- Exclude deleted bibs
	br.BibliographicRecordID IS NOT NULL

GROUP BY
br.BibliographicRecordID,
br.BrowseTitle,
br.BrowseAuthor,
material.numValue,
mat.Description,
shelfloc.numValue,
sl.Description
ORDER BY
COUNT(shelfloc.numValue) DESC

Give that a shot and see if the results match up any better for you!

3

Thank you! That definitly helped :slight_smile:

1 Like
4

@lmurff you didn’t mention if the counts were too high or too low. @Phil_Agnew offered some good improvements, but a few other considerations:

I think that this will still cause NULL shelf location item records to be excluded from the counts.

This will also end up requiring that there is an item record still in the system. So, if you deleted the item record, it would be excluded from the counts.

5

Thanks for all the help! The numbers were too low. I found out the issue was titles on multiple shelves. So I was able to make the connection on material type and take out the shelf location code. Thanks again, I really appreciate it

1 Like