Patron Notificaton History

Clearinghouse Repository Polaris Repository
,
1

Patron Notificaton History

This query pulls the notification history for a patron between two given dates, based upon PatronID. The query returns the PatronID, patron barcode, patron name, notification datetime, reporting library, ItemRecordID, item title, notification type, monetary amount (if any), the delivery type, and notification status.
```SQL DECLARE @Patron AS INT; DECLARE @StartDate AS DATETIME; DECLARE @EndDate AS DATETIME;

SET @Patron = 112357;
SET @StartDate = ‘2019-07-01 00:00:00.000’;
SET @EndDate = ‘2021-12-10 23:59:59.999’;

SELECT
nh.PatronId AS [Patron ID],
p.Barcode AS [Patron Barcode],
pr.PatronFullName AS [Patron Name],
nh.NoticeDate AS [Notification Date],
o.Name AS [Reporting Library],
nh.ItemRecordID AS [Item Record ID],
nh.Title AS [Item Title],
nt.Description AS [Notification Type],
CAST(nh.Amount AS DECIMAL (20,2)) AS [Amount],
dopt.DeliveryOption AS [Delivery],
ns.Description AS [Notification Status]

FROM
Results.Polaris.NotificationHistory nh WITH (NOLOCK)

INNER JOIN
Polaris.Polaris.Organizations o WITH (NOLOCK) ON o.OrganizationID = nh.ReportingOrgId
INNER JOIN
Polaris.Polaris.NotificationTypes nt WITH (NOLOCK) ON nt.NotificationTypeID = nh.NotificationTypeId
INNER JOIN
Polaris.Polaris.DeliveryOptions dopt WITH (NOLOCK) ON dopt.DeliveryOptionID = nh.DeliveryOptionId
INNER JOIN
Polaris.Polaris.NotificationStatuses ns WITH (NOLOCK) ON ns.NotificationStatusID = nh.NotificationStatusId
INNER JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK) ON p.PatronID = nh.PatronId
INNER JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON pr.PatronID = nh.PatronId

WHERE
nh.PatronID = @Patron
AND
nh.NoticeDate BETWEEN @StartDate AND @EndDate

ORDER BY
nh.NoticeDate DESC