This query is designed to pull counts of items circulated at given branches, specifically looking for items circulated at branches that are outside of the items’ assigned branch. Used for various state reports, this query can get initial circ data and provide various information depending on what you need to pull from the temporary table #TempCircLendingData that’s populated with item assigned branch, material type, transaction dates, transacting organization, TransactionTypeIDs, ItemRecordIDs, PatronIDs, and thespecific TransactionIDs which can be used to hook into other data and result sets.
/* In our case, the state report only cares about the number of items you lend/borrow, not how many
checkouts occurred. */
-- This is the master temp table used to collect and deliver data.
CREATE TABLE #TempCircLendingData (
ItemOrgName NVARCHAR(50) NULL,
ItemOrgID INT NULL,
MaterialTypeID INT NULL,
MatDescription NVARCHAR(50) NULL,
TransactionDate DATETIME,
TransactOrgName NVARCHAR(50) NULL,
TransactionTypeID INT,
ItemRecordID INT,
PatronID INT,
TransactionID INT
);
-- Begin populating that table
INSERT INTO #TempCircLendingData
SELECT
iab.Name,
itemab.numValue,
0, -- Initializing - will update later with MaterialTypeID
0, -- Initializing - will update later with MaterialType Description
th.TranClientDate,
torg.Name,
th.TransactionTypeID,
itemid.numValue,
0, -- Initializing - will update later with PatronID
th.TransactionID
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Item assigned branch
PolarisTransactions.Polaris.TransactionDetails itemab WITH (NOLOCK)
ON (th.TransactionID = itemab.TransactionID AND itemab.TransactionSubTypeID = 125)
INNER JOIN -- Item assigned branch readable name
Polaris.Polaris.Organizations iab WITH (NOLOCK)
ON (iab.OrganizationID = itemab.numValue)
INNER JOIN -- ItemRecordID
PolarisTransactions.Polaris.TransactionDetails itemid WITH (NOLOCK)
ON (th.TransactionID = itemid.TransactionID AND itemid.TransactionSubTypeID = 38)
INNER JOIN -- Transacting branch readable name
Polaris.Polaris.Organizations torg WITH (NOLOCK)
ON (th.OrganizationID = torg.OrganizationID)
WHERE -- Check out
th.TransactionTypeID = 6001
AND -- Adjust dates as needed
th.TranClientDate BETWEEN '2023-07-01 00:00:00.000' AND '2024-06-30 23:59:59.999'
AND -- Put in the OrganizationIDs for the item's assigned branches and set boolean operations as desired
itemab.numValue IN (14,15,110,113)
AND -- Put in the OrganizatonsIDs for the transacting branches and set boolean operations as desired
th.OrganizationID NOT IN (14,15,110,113)
-- DEBUG
--SELECT * FROM #TempCircLendingData;
/* ---------------------------------------------------------------------------------------- */
/* If you do too much and try to pull in too much data for the table above, this query can go from an
execution time measured in seconds to an execution time measured in minutes. So we'll bring in the
MaterialType data separately, acting upon the TransactionIDs in #TempCircLendingData, and then update
the #TempCircLendingData table accordingly. */
CREATE TABLE #TempMaterialData (
MaterialTypeID INT,
MatDescription NVARCHAR(50),
TransactionID INT
);
INSERT INTO
#TempMaterialData
SELECT
material.numValue,
mat.Description,
th.TransactionID
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get MaterialTypeID
PolarisTransactions.Polaris.TransactionDetails material WITH (NOLOCK)
ON (th.TransactionID = material.TransactionID AND material.TransactionSubTypeID = 4)
INNER JOIN -- Get readable MaterialType description
Polaris.Polaris.MaterialTypes mat WITH (NOLOCK)
ON (mat.MaterialTypeID = material.numValue)
WHERE
th.TransactionID IN (
SELECT TransactionID FROM #TempCircLendingData
);
-- DEBUG
--SELECT * FROM #TempMaterialData;
/* ---------------------------------------------------------------------------------------- */
/* If you do too much and try to pull in too much data for the table above, this query can go from an
execution time measured in seconds to an execution time measured in minutes. So we'll bring in the
PatronID data separately, acting upon the TransactionIDs in #TempCircLendingData, and then update
the #TempCircLendingData table accordingly. */
CREATE TABLE #TempPatronData (
PatronID INT,
TransactionID INT
);
INSERT INTO
#TempPatronData
SELECT
patron.numValue,
th.TransactionID
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get PatronID
PolarisTransactions.Polaris.TransactionDetails patron WITH (NOLOCK)
ON (th.TransactionID = patron.TransactionID AND patron.TransactionSubTypeID = 6)
WHERE
th.TransactionID IN (
SELECT TransactionID FROM #TempCircLendingData
);
/* ---------------------------------------------------------------------------------------- */
/* Update the #TempCircLendingData table with MaterialType and Patron data */
-- Update MaterialTypes
UPDATE
#TempCircLendingData
SET
#TempCircLendingData.MaterialTypeID = #TempMaterialData.MaterialTypeID,
#TempCircLendingData.MatDescription = #TempMaterialData.MatDescription
FROM
#TempMaterialData
WHERE
#TempMaterialData.TransactionID = #TempCircLendingData.TransactionID;
-- Update Patron
UPDATE
#TempCircLendingData
SET
#TempCircLendingData.PatronID = #TempPatronData.PatronID
FROM
#TempPatronData
WHERE
#TempPatronData.TransactionID = #TempCircLendingData.TransactionID;
/* ---------- DATA DELIVERY ---------- */
/* The #TempCircLendingData table is fully populated. Put your final data delivery queries below. */
/*SELECT
ItemOrgName AS [Item Assigned Library],
ItemOrgID AS [Item Assigned OrgID],
MaterialTypeID,
MatDescription AS [Material Type],
TransactionDate,
TransactOrgName AS [Checkout Library],
PatronID,
ItemRecordID
FROM
#TempCircLendingData
WHERE
MaterialTypeID NOT IN (38,39,45,157,161,162,163,164);*/
SELECT
PatronID,
COUNT(DISTINCT ItemRecordID)
FROM
#TempCircLendingData
GROUP BY
PatronID;
-- Tidy up
DROP TABLE #TempCircLendingData;
DROP TABLE #TempMaterialData;
DROP TABLE #TempPatronData;