This query pulls the hold activity histories for a given PatronID based upon the existing holds is the Polaris.Polaris.SysHoldRequests table. Hold activity isn’t well tracked in the PolarisTransactions database so this may be a better method of getting a report of hold activity so long as the hold hasn’t been filled and/or removed from SysHoldRequests.
/* ---------- BUILD THE FRAMEWWORK ---------- */
-- Create a table to hold all current hold request data for a given patron
CREATE TABLE #TempPrimaryHoldData (
SysHoldRequestID INT,
PatronID INT,
PickupBranchID INT,
CreationDate DATETIME,
BibliographicRecordID INT,
);
-- Create a table to hold all hold request history data for a given patron
CREATE TABLE #TempHoldHistory (
SysHoldRequestID INT,
SysHoldStatusID INT,
StatusTransitionDate DATETIME,
ItemRecordID INT,
ActionTakenID INT
)
-- The aforementioned given patron: Put their PatronID below
DECLARE @TargetPatronID INT = 2691429;
/* ---------- POPULATE #TempPrimaryHoldData ---------- */
INSERT INTO #TempPrimaryHoldData
SELECT
SysHoldRequestID,
PatronID,
PickupBranchID,
CreationDate,
BibliographicRecordID
FROM
Polaris.Polaris.SysHoldRequests WITH (NOLOCK)
WHERE
PatronID = @TargetPatronID
/* ---------- POPULATE #TempHoldHistory with Today's Hold History ---------- */
INSERT INTO #TempHoldHistory
SELECT
SysHoldRequestID,
SysHoldStatusID,
StatusTransitionDate,
ItemRecordID,
ActionTakenID
FROM
Polaris.Polaris.SysHoldHistoryDaily WITH (NOLOCK)
WHERE
SysHoldRequestID IN (SELECT SysHoldRequestID FROM #TempPrimaryHoldData)
/* ---------- POPULATE #TempHoldHistory with Previous Hold History ---------- */
INSERT INTO #TempHoldHistory
SELECT
SysHoldRequestID,
SysHoldStatusID,
StatusTransitionDate,
ItemRecordID,
ActionTakenID
FROM
Polaris.Polaris.SysHoldHistory WITH (NOLOCK)
WHERE
SysHoldRequestID IN (SELECT SysHoldRequestID FROM #TempPrimaryHoldData)
/* ---------- DATA DELIVERY ---------- */
SELECT
tphd.SysHoldRequestID AS [Hold Request ID],
shs.Description AS [Hold Status],
shha.ActionTakenDesc AS [Hold Activity],
thh.StatusTransitionDate AS [Hold Activity Date],
tphd.PatronID AS [PatronID],
p.Barcode AS [Patron Barcode],
pr.PatronFullName AS [Patron Name],
tphd.BibliographicRecordID AS [Bib Record ID],
br.BrowseTitle AS [Title],
thh.ItemRecordID AS [Item Record ID]
FROM
#TempHoldHistory thh
INNER JOIN
#TempPrimaryHoldData tphd
ON (tphd.SysHoldRequestID = thh.SysHoldRequestID)
INNER JOIN
Polaris.Polaris.SysHoldStatuses shs WITH (NOLOCK)
ON (shs.SysHoldStatusID = thh.SysHoldStatusID)
INNER JOIN
Polaris.Polaris.SysHoldHistoryActions shha WITH (NOLOCK)
ON (shha.ActionTakenID = thh.ActionTakenID)
INNER JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK)
ON (p.PatronID = tphd.PatronID)
INNER JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
ON (pr.PatronID = tphd.PatronID)
INNER JOIN
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK)
ON (br.BibliographicRecordID = tphd.BibliographicRecordID)
ORDER BY
tphd.SysHoldRequestID,
thh.StatusTransitionDate
-- Tidy up
DROP TABLE #TempPrimaryHoldData;
DROP TABLE #TempHoldHistory;