Items Checked Out and Declared Lost at Branch

Clearinghouse Repository Polaris Repository
, ,
1

I had a request to create a report of items checked out at a given branch that were later declared lost by the same branch. This query pulls in a list of items checked out and then compares them to a list of items declared lost.


/* ---------- BUILD FRAMEWORK ---------- */

-- Declare and set variables
DECLARE @StartDate DATETIME = '2022-07-01 00:00:00.000';
DECLARE @EndDate DATETIME = '2023-06-30 23:59:59.999';
-- Drop your library/branch OrganizationID below
DECLARE @OrgID INT = 89;

-- Create table for time parameters
CREATE TABLE #TimeParameters (
    StartTime DATETIME,
    EndTime DATETIME
);

-- Populate the time parameters
INSERT INTO #TimeParameters
(StartTime, EndTime)
VALUES(@StartDate,@EndDate);

-- Create a table that holds checkouts from the branch/library
CREATE TABLE #TempBranchCheckOuts (
    ItemRecordID INT
);

-- Create a table that holds items declared lost by the same branch/library
CREATE TABLE #TempBranchLostItems (
    LostItemsCount INT
);

/* ---------- POPULATE #TempBranchCheckOuts ---------- */

INSERT INTO #TempBranchCheckOuts

SELECT
    item.numValue

FROM
    PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)

INNER JOIN -- Pulls ItemRecordID
    PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
    ON (th.TransactionID = item.TransactionID AND item.TransactionSubTypeID = 38)

WHERE -- Check outs
    th.TransactionTypeID = 6001
AND
    th.TranClientDate >= (SELECT MAX(StartTime) FROM #TimeParameters)
AND
    th.TranClientDate < (SELECT MAX(EndTime) FROM #TimeParameters)

AND
    th.OrganizationID IN (@OrgID);

/* ---------- POPULATE #TempBranchLostItems ---------- */

INSERT INTO #TempBranchLostItems

SELECT
    COUNT(lostitem.numValue)

FROM
    PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)

INNER JOIN -- Pulls ItemRecordID
    PolarisTransactions.Polaris.TransactionDetails lostitem WITH (NOLOCK)
    ON (th.TransactionID = lostitem.TransactionID AND lostitem.TransactionSubTypeID = 38)

WHERE -- Item marked as claimed lost
    th.TransactionTypeID = 6026
AND
    th.TranClientDate >= (SELECT MAX(StartTime) FROM #TimeParameters)
AND
    th.TranClientDate < (SELECT MAX(EndTime) FROM #TimeParameters)
AND
    th.OrganizationID IN (@OrgID)
AND
    lostitem.numValue IN (SELECT ItemRecordID FROM #TempBranchCheckOuts);

/* ---------- DATA DELIVERY ---------- */ 

SELECT * FROM #TempBranchLostItems;

-- Tidy up
DROP TABLE #TimeParameters;
DROP TABLE #TempBranchCheckOuts;
DROP TABLE #TempBranchLostItems;