Pull Holds from Notification Queue
Your telephony server will fail at some point. And you might need to get the word out to your telephone notification patrons that they have holds. This query pulls the patron and item information from your NotificationQueue, where all those calls
are stacking up, and presents the data in a way that front line staff can use it to manually call the patrons.
SELECT
ir.Barcode AS "Item Barcode",
br.BrowseTitle AS "Item Title",
nt.[Description] AS "Notification Type",
dopt.DeliveryOption AS "Delivery",
pr.PatronFullName AS "Patron Name",
pr.PhoneVoice1 AS "Phone 1",
pr.PhoneVoice2 AS "Phone 2",
pr.PhoneVoice3 AS "Phone 3",
o.Name AS "Library",
nq.CreationDate AS "Creation Date"
FROM
Results.Polaris.NotificationQueue nq WITH (NOLOCK)
JOIN
Polaris.Polaris.ItemRecords ir WITH (NOLOCK) ON ir.ItemRecordID = nq.ItemRecordID
JOIN
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) ON br.BibliographicRecordID = ir.ASsociatedBibRecordID
JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON pr.PatronID = nq.PatronID
JOIN
Polaris.Polaris.Organizations o WITH (NOLOCK) ON o.OrganizationID = nq.ReportingOrgID
JOIN
Polaris.Polaris.NotificationTypes nt WITH (NOLOCK) ON nt.NotificationTypeID = nq.NotificationTypeID
JOIN
Polaris.Polaris.DeliveryOptions dopt WITH (NOLOCK) ON dopt.DeliveryOptionID = nq.DeliveryOptionID
WHERE
nq.DeliveryOptionID in (3, 4, 5) -- Telephony delivery
AND nt.Description = 'Hold'
AND nq.CreationDate BETWEEN '2021-04-04 07:25:00.000' AND '2021-04-06 23:59:59.999' -- Set dates as needed
ORDER BY
o.name, pr.PatronFullName, nq.CreationDate