Notification Log for a Specific Patron
This pulls all of the notifications sent to a given PatronID no matter what the delivery method might be.
SELECT
nl.NotificationDateTime AS "Notificaiton Date",
nl.PatronID AS "Patron ID",
pr.PatronFullName AS "Patron Name",
o.Name AS "Reporting Org",
nt.[Description] AS "Notification Type",
dopt.DeliveryOption AS "Delivery",
nl.DeliveryString AS "Phone Number",
nl.Details AS "Details",
nl.NotificationLogID AS "Notification Log ID"
FROM
PolarisTransactions.Polaris.NotificationLog nl WITH (NOLOCK)
JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) on pr.PatronID = nl.PatronID
JOIN
Polaris.Polaris.NotificationTypes nt WITH (NOLOCK) on nt.NotificationTypeID = nl.NotificationTypeID
JOIN
Polaris.Polaris.DeliveryOptions dopt WITH (NOLOCK) on dopt.DeliveryOptionID = nl.DeliveryOptionID
JOIN
Polaris.Polaris.Organizations o WITH (NOLOCK) on o.OrganizationID = nl.ReportingOrgID
WHERE
nl.DeliveryOptionID in (1,2,3,4,5,6,7,8) -- Limit by Delivery Option if you want.
AND
nl.NotificationDateTime BETWEEN '2020-03-01 00:00:00.000' AND '2020-03-20 23:59:59.999' -- Alter as needed
AND
nl.PatronID = 113452 -- Insert PatronID
ORDER BY
NotificationDateTime DESC