Create Item Record Set and List for Deleting Withdrawn Items
Typically, we delete withdrawn items after they’ve been withdrawn for a certain amount of time. Because I’m a paranoid kind of DBA, I want to keep a record of what was deleted and when… ya know, just in case. This query will pull withdrawn items based upon their last circ status change date and then dump those items into an item record set of your choosing. (Based upon a RecordSetID.) Then, after it does that, it’ll present you with a result set with lots of columns that you can then save as a CSV to import into your own SQLite database to track what you deleted on the off chance that something comes up. I may be a little broken inside.
/* Declare variables */
DECLARE @WithdrawnItemRecordSetID INT;
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
/* ------------------------------------------------------------------------------- */
/* Set variables */
SET @WithdrawnItemRecordSetID = 32434;
SET @StartDate = '1957-01-01';
SET @EndDate = '2021-11-01 23:59:59.999';
/* ------------------------------------------------------------------------------- */
/* Set up a temporary table for recording items to be withdrawn */
DECLARE @WithdrawnItemsTemp TABLE
(
ItemRecordID INT,
AssociatedBibRecordID INT,
Barcode VARCHAR(50),
Name VARCHAR(500),
BrowseTitle VARCHAR(500),
LastCircTransactionDate DATETIME,
ItemStatusDate DATETIME,
Description VARCHAR(50)
)
/* ------------------------------------------------------------------------------- */
/* Populate that temporary table */
INSERT @WithdrawnItemsTemp
SELECT
cir.ItemRecordID,
cir.AssociatedBibRecordID,
cir.Barcode,
o.Name,
br.BrowseTitle,
cir.LastCircTransactionDate,
cir.ItemStatusDate,
ist.Description
FROM
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.ItemRecordDetails ird WITH (NOLOCK) ON cir.ItemRecordID = ird.ItemRecordID
INNER JOIN
Polaris.Polaris.ItemStatuses ist WITH (NOLOCK) ON cir.ItemStatusID = ist.ItemStatusID
INNER JOIN
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) ON cir.AssociatedBibRecordID = br.BibliographicRecordID
INNER JOIN
Polaris.Polaris.Organizations o WITH (NOLOCK) ON cir.AssignedBranchID = o.OrganizationID
WHERE
cir.AssignedBranchID IN (10,3)
AND
cir.ItemStatusDate BETWEEN @StartDate AND @EndDate
AND
cir.ItemStatusID IN (11)
/* ------------------------------------------------------------------------------- */
/* Prepare a table to populate the item record set */
DECLARE @WithdrawnItemsPopulate TABLE
(
ItemRecordID INT,
RecordSetID INT
)
/* ------------------------------------------------------------------------------- */
/* Populate the table that will update the record set */
INSERT INTO @WithdrawnItemsPopulate
SELECT
ItemRecordID,
@WithdrawnItemRecordSetID
FROM
@WithdrawnItemsTemp
/* ------------------------------------------------------------------------------- */
/* Now, update ItemRecordSets with the data from @WithdrawnItemsPopulate */
INSERT INTO Polaris.Polaris.ItemRecordSets
SELECT * FROM @WithdrawnItemsPopulate
/* ------------------------------------------------------------------------------- */
/* Finally, offer the data in @WithdrawnItemsTemp in a readable format */
SELECT
ItemRecordID AS [Item Record ID],
AssociatedBibRecordID AS [Bib Record ID],
Barcode AS [Item Barcode],
Name AS [Assigned Branch],
BrowseTitle AS [Browse Title],
LastCircTransactionDate AS [Last Circ],
ItemStatusDate AS [Last Circ Status Change],
Description AS [Circ Status]
FROM
@WithdrawnItemsTemp