Get Billing Notices Info From Previous Days
Sometimes the printed billing notices get ran, forgotten about, and then ran the next day which overwrites the PDF from the previous day(s). If you need to get the print billing notices from a previous day, this query will pull all you need to send the patron a snail mail notification.
SELECT
nh.PatronID AS [PatronID],
p.Barcode AS [Patron Barcode],
pr.PatronFullName AS [Patron Name],
pr.PatronFirstLastName AS [Patron Mailing Name],
adds.StreetOne AS [Street One],
adds.StreetTwo AS [Street Two],
pos.City AS [City],
pos.State AS [State],
pos.PostalCode AS [ZIP Code],
cir.Barcode AS [Item Barcode],
nh.Title AS [Item Title],
CAST(nh.Amount AS MONEY) AS [Price],
cir.LastDueDate AS [Due Date]
FROM
Results.Polaris.NotificationHistory nh WITH (NOLOCK)
INNER JOIN -- Bring in the Patrons table so we can have a barcode
Polaris.Polaris.Patrons p WITH (NOLOCK)
ON (p.PatronID = nh.PatronId)
INNER JOIN -- Bring in the PatronRegistration table so we can get patron info
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
ON (pr.PatronID = nh.PatronId)
INNER JOIN -- For patron addresses
Polaris.Polaris.PatronAddresses pad WITH (NOLOCK)
ON (pad.PatronID = nh.PatronId)
INNER JOIN -- For patron addresses
Polaris.Polaris.Addresses adds WITH (NOLOCK)
ON (pad.AddressID = adds.AddressID)
INNER JOIN -- For patron addresses
Polaris.Polaris.PostalCodes pos WITH (NOLOCK)
ON (pos.PostalCodeID = adds.PostalCodeID)
INNER JOIN -- Get item record information
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
ON (cir.ItemRecordID = nh.ItemRecordId)
WHERE -- Change these dates as needed
nh.NoticeDate BETWEEN '2022-11-08 00:00:00.000' AND '2022-11-08 23:59:59.999'
AND -- 11 = Bill
nh.NotificationTypeID = 11
AND -- 1 = Print/Mail notification
nh.DeliveryOptionId = 1
ORDER BY
nh.PatronID