# Hold Activity History for Extant Holds

**URL:** https://forum.innovativeusers.org/t/hold-activity-history-for-extant-holds/1274
**Category:** Polaris Repository
**Tags:** polaris-holds, holds, sql, polaris
**Created:** [June 16, 2023, 1:39pm UTC](https://forum.innovativeusers.org/t/hold-activity-history-for-extant-holds/1274 "2023-06-16T13:39:27Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![CyberpunkLibrarian](https://forum.innovativeusers.org/user_avatar/forum.innovativeusers.org/cyberpunklibrarian/32/1168_2.png) [@CyberpunkLibrarian](https://forum.innovativeusers.org/u/CyberpunkLibrarian)
#### Post date: [June 16, 2023, 1:39pm UTC](https://forum.innovativeusers.org/t/hold-activity-history-for-extant-holds/1274/1 "2023-06-16T13:39:27Z")

</div>

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.

```auto
/* ---------- 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;

```