Custom Reports: Query of Holds on Items That are Currently Available

[This article was copied from the Voyager Wiki.]

This query reports back on items that have holds on them regardless if they were requested by staff or student.  It shows all items that have holds that are currently on the shelves.  It also includes the expiration date of the hold request, patron name.

SELECT PATRON.LAST_NAME, PATRON.FIRST_NAME, BIB_TEXT.TITLE, ITEM_BARCODE.ITEM_BARCODE, MFHD_MASTER.NORMALIZED_CALL_NO, HOLD_RECALL.EXPIRE_DATE
FROM ((MFHD_MASTER INNER JOIN ((((PATRON INNER JOIN HOLD_RECALL ON PATRON.PATRON_ID = HOLD_RECALL.PATRON_ID) INNER JOIN HOLD_RECALL_ITEMS ON HOLD_RECALL.HOLD_RECALL_ID = HOLD_RECALL_ITEMS.HOLD_RECALL_ID) INNER JOIN BIB_TEXT ON HOLD_RECALL.BIB_ID = BIB_TEXT.BIB_ID) INNER JOIN BIB_MFHD ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) ON MFHD_MASTER.MFHD_ID = BIB_MFHD.MFHD_ID) INNER JOIN ITEM ON HOLD_RECALL_ITEMS.ITEM_ID = ITEM.ITEM_ID) INNER JOIN ITEM_BARCODE ON ITEM.ITEM_ID = ITEM_BARCODE.ITEM_ID
GROUP BY PATRON.LAST_NAME, PATRON.FIRST_NAME, BIB_TEXT.TITLE, ITEM_BARCODE.ITEM_BARCODE, MFHD_MASTER.NORMALIZED_CALL_NO, PATRON.RECALLS_PLACED, PATRON.HOLDS_PLACED, HOLD_RECALL.EXPIRE_DATE
HAVING (((PATRON.RECALLS_PLACED)<"1") AND ((PATRON.HOLDS_PLACED)<"1"))
ORDER BY PATRON.LAST_NAME, PATRON.FIRST_NAME;