Tweaking the Holds Purchase Alert Report

kmarshall · January 25, 2024, 4:37pm

I want to tweak the Holds Purchase Alert canned report to exclude a couple of branches in the holds ratio calculation (the two branches allow holds, but only for their own patrons). Can anyone give me an idea of how to do that? I’m guessing the HoldPurchaseAlert_byMaterialType stored procedure could be modified, but it’s beyond my skills. And while I’m asking for things, is there a way to also include the item collection as well as material type in the report? Any help I can get would be much appreciated!

wesochuck · January 25, 2024, 9:06pm

Given the scope of changes you’re hoping to accomplish, it might be easier to look at setting up a SimplyReports Hold Count report and then publishing that the toolbar

I would NOT recommend changing a built-in report stored procedure, though making copies of one and using it to build a custom one is a pretty safe approach.

kmarshall · January 26, 2024, 1:13pm

I can’t figure out how to get it to give me the info I need, though. I need a holds ratio (let’s say 4:1) and I don’t see how to do that. And even if I put in number of requests as being greater than 4, it’s giving me titles with only one hold request.
And yeah, I should have been clearer that I meant copying the existing stored procedure and modifying the copy. I’m not that crazy

wesochuck · January 29, 2024, 5:45pm

Here is a starting point that was arrived at with the extensive help of chatgpt.

This is now a standard SQL script, if you find that it works well for you, there are other resources available for adapting this into an SSRS report
A collection code and material type from the item record are shown, but that information is incomplete because there could be multiple different codes associated with the item records
This output has NOT been extensively tested, the script runs and produces sane output, but the results should be tested, especially to make sure nothing is being accidentally excluded from the output

DECLARE @nNumberOfHolds int = 150, --change to min number of holds
        @nNumberofItems int = 50, -- change to min number of items, also sets the hold ration - in this example there would be a ration of 3
        @TypesOfMaterialList NVARCHAR(max) = '1, 5', --comma separated values from polaris.polaris.MARCTypeOfMaterial
        @IncludeNonHoldable bit = 0 --zero to EXCLUDE nonholdable, 1 to INCLUDE nonholdable items

DECLARE @MaterialTypes TABLE (MARCTypeOfMaterialid INT);
DECLARE @t TABLE (
    BibliographicRecordID int NOT NULL,
    Author nvarchar(255) NULL,
    Title nvarchar(255) NULL,
    ISBN NVARCHAR(50) NULL,
    MARCTypeOfMaterial NVARCHAR(80) NULL,
    HoldCount int NULL,
    ItemCount int NULL,
    CallNumber nvarchar(255) NULL,
    HoldToItemRatio float NULL,
    AssignedCollectionID int NULL,
    MaterialTypeID int NULL
);

-- Populate @MaterialTypes table variable
IF LEN(@TypesOfMaterialList) = 1 AND @TypesOfMaterialList = N'0'
BEGIN
    INSERT INTO @MaterialTypes (MARCTypeOfMaterialid)
    SELECT MARCTypeOfMaterialid FROM Polaris.Polaris.MARCTypeOfMaterial WITH (NOLOCK);
END
ELSE
BEGIN
    INSERT INTO @MaterialTypes (MARCTypeOfMaterialid)
    SELECT value FROM STRING_SPLIT(@TypesOfMaterialList, ',') 
    WHERE TRY_CAST(value AS INT) IS NOT NULL;
END;

-- Get locked requests and insert into @t table variable
INSERT INTO @t (BibliographicRecordID, HoldCount)
SELECT SHR.BibliographicRecordID, COUNT(*)
FROM Polaris.Polaris.SysHoldRequests SHR WITH (NOLOCK)
JOIN @MaterialTypes Mt ON SHR.PrimaryMARCTOMID = Mt.MARCTypeOfMaterialid
WHERE SysHoldStatusID IN (1, 3) AND BibliographicRecordID <> 0
GROUP BY SHR.BibliographicRecordID;

-- Update ItemCount based on @IncludeNonHoldable
DECLARE @ItemStatusList NVARCHAR(255) = CASE WHEN @IncludeNonHoldable = 1 THEN N'11' ELSE N'7,8,9,10,11,14,16' END;

UPDATE @t
SET ItemCount = (
    SELECT COUNT(CIR.AssociatedBibRecordID)
    FROM Polaris.Polaris.CircItemRecords CIR WITH (NOLOCK)
    WHERE CIR.AssociatedBibRecordID = t.BibliographicRecordID AND
          CIR.ItemStatusID NOT IN (SELECT value FROM STRING_SPLIT(@ItemStatusList, ','))
          AND CIR.RecordStatusID = 1 AND (@IncludeNonHoldable = 1 OR (CIR.Holdable = 1 AND CIR.NonCirculating = 0))
    )
FROM @t t;

-- Filter records based on hold count and item count
DECLARE @fRatio float = CASE WHEN @nNumberOfItems > 0 THEN @nNumberOfHolds / (@nNumberOfItems * 1.0) ELSE NULL END;

DELETE FROM @t
WHERE ItemCount <> 0 AND 
      (@fRatio IS NULL AND HoldCount / ItemCount < @nNumberOfHolds OR
       @fRatio IS NOT NULL AND HoldCount / (ItemCount * 1.0) < @fRatio);

-- Pre-calculate aggregated values
WITH AggregatedData AS (
    SELECT 
        CIR.AssociatedBibRecordID,
        MAX(CIR.AssignedCollectionID) AS MaxAssignedCollectionID,
        MAX(CIR.MaterialTypeID) AS MaxMaterialTypeID
    FROM 
        Polaris.Polaris.CircItemRecords CIR WITH (NOLOCK)
    GROUP BY 
        CIR.AssociatedBibRecordID
)

-- Update the table with aggregated data
UPDATE @t
SET 
    Author = Polaris.Polaris.Circ_GetSysHoldRequestDisplayAuthor(SHR.SysHoldRequestID, 0),
    Title = Polaris.Polaris.Circ_GetSysHoldRequestDisplayTitle(SHR.SysHoldRequestID, 0),
    ISBN = COALESCE(SHR.ISBN, Polaris.Polaris.Cat_GetBibRecordUPC(SHR.BibliographicRecordID, 0)),
    MARCTypeOfMaterial = MTOM.Description,
    CallNumber = BR.BrowseCallNo,
    HoldToItemRatio = CASE WHEN ItemCount > 0 THEN HoldCount * 1.0 / ItemCount ELSE NULL END,
    AssignedCollectionID = AD.MaxAssignedCollectionID,
    MaterialTypeID = AD.MaxMaterialTypeID
FROM @t t
INNER JOIN Polaris.Polaris.SysHoldRequests SHR WITH (NOLOCK) ON t.BibliographicRecordID = SHR.BibliographicRecordID
INNER JOIN Polaris.Polaris.BibliographicRecords BR WITH (NOLOCK) ON SHR.BibliographicRecordID = BR.BibliographicRecordID
LEFT OUTER JOIN Polaris.Polaris.MARCTypeOfMaterial MTOM WITH (NOLOCK) ON BR.PrimaryMARCTOMID = MTOM.MARCTypeOfMaterialID
LEFT OUTER JOIN AggregatedData AD ON t.BibliographicRecordID = AD.AssociatedBibRecordID;

-- Display the final results
SELECT 
    t.BibliographicRecordID,
    t.Author,
    t.Title,
    t.ISBN,
    t.MARCTypeOfMaterial,
    t.HoldCount,
    t.ItemCount,
    t.CallNumber,
    t.HoldToItemRatio,
	mt.Description,
	c.Name
FROM @t t
join polaris.polaris.MaterialTypes mt on mt.MaterialTypeID = t.MaterialTypeID
join polaris.polaris.Collections c on c.CollectionID = t.AssignedCollectionID
WHERE t.HoldCount >= @nNumberOfHolds AND t.ItemCount >= @nNumberofItems
ORDER BY t.HoldCount DESC, t.Title;

kmarshall · January 29, 2024, 7:25pm

Thanks! I can’t run it though I get an error ‘The EXECUTE permission was denied on the object ‘Cat_GetBibRecordUPC’’ Our IT folks have the server locked down pretty hard. I’ll send this to them and see if they can work with me on it.

wesochuck · January 29, 2024, 7:39pm

I’m sure there is some way around that, but there are a number of “Gets” that would have to be worked around so if they can tweak permissions that would be ideal.

I also realized I forgot the initial premise of wanting to adapt the script, to exclude certain branches, this updated script should allow for that:

DECLARE @nNumberOfHolds int = 150, --change to min number of holds
        @nNumberofItems int = 50, -- change to min number of items, also sets the hold ration - in this example there would be a ration of 3
        @TypesOfMaterialList NVARCHAR(max) = '1, 5', --comma separated values from polaris.polaris.MARCTypeOfMaterial
        @IncludeNonHoldable bit = 0, --zero to EXCLUDE nonholdable, 1 to INCLUDE nonholdable items
        @ExcludeBranchIDList NVARCHAR(max) = '20, 22'; --comma separated values of Branch IDs to exclude

DECLARE @MaterialTypes TABLE (MARCTypeOfMaterialid INT);
DECLARE @ExcludeBranchIDs TABLE (AssignedBranchID INT);
DECLARE @t TABLE (
    BibliographicRecordID int NOT NULL,
    Author nvarchar(255) NULL,
    Title nvarchar(255) NULL,
    ISBN NVARCHAR(50) NULL,
    MARCTypeOfMaterial NVARCHAR(80) NULL,
    HoldCount int NULL,
    ItemCount int NULL,
    CallNumber nvarchar(255) NULL,
    HoldToItemRatio float NULL,
    AssignedCollectionID int NULL,
    MaterialTypeID int NULL
);

-- Populate @MaterialTypes table variable
IF LEN(@TypesOfMaterialList) = 1 AND @TypesOfMaterialList = N'0'
BEGIN
    INSERT INTO @MaterialTypes (MARCTypeOfMaterialid)
    SELECT MARCTypeOfMaterialid FROM Polaris.Polaris.MARCTypeOfMaterial WITH (NOLOCK);
END
ELSE
BEGIN
    INSERT INTO @MaterialTypes (MARCTypeOfMaterialid)
    SELECT value FROM STRING_SPLIT(@TypesOfMaterialList, ',') 
    WHERE TRY_CAST(value AS INT) IS NOT NULL;
END;

-- Populate @ExcludeBranchIDs table variable
INSERT INTO @ExcludeBranchIDs (AssignedBranchID)
SELECT value FROM STRING_SPLIT(@ExcludeBranchIDList, ',') 
WHERE TRY_CAST(value AS INT) IS NOT NULL;

-- Get locked requests and insert into @t table variable
INSERT INTO @t (BibliographicRecordID, HoldCount)
SELECT SHR.BibliographicRecordID, COUNT(*)
FROM Polaris.Polaris.SysHoldRequests SHR WITH (NOLOCK)
JOIN @MaterialTypes Mt ON SHR.PrimaryMARCTOMID = Mt.MARCTypeOfMaterialid
WHERE SysHoldStatusID IN (1, 3) AND BibliographicRecordID <> 0
GROUP BY SHR.BibliographicRecordID;

-- Update ItemCount based on @IncludeNonHoldable and exclude certain branches
DECLARE @ItemStatusList NVARCHAR(255) = CASE WHEN @IncludeNonHoldable = 1 THEN N'11' ELSE N'7,8,9,10,11,14,16' END;

UPDATE @t
SET ItemCount = (
    SELECT COUNT(CIR.AssociatedBibRecordID)
    FROM Polaris.Polaris.CircItemRecords CIR WITH (NOLOCK)
    WHERE CIR.AssociatedBibRecordID = t.BibliographicRecordID AND
          CIR.ItemStatusID NOT IN (SELECT value FROM STRING_SPLIT(@ItemStatusList, ','))
          AND CIR.RecordStatusID = 1 AND (@IncludeNonHoldable = 1 OR (CIR.Holdable = 1 AND CIR.NonCirculating = 0))
          AND CIR.AssignedBranchID NOT IN (SELECT AssignedBranchID FROM @ExcludeBranchIDs)
    )
FROM @t t;

-- Filter records based on hold count and item count
DECLARE @fRatio float = CASE WHEN @nNumberOfItems > 0 THEN @nNumberOfHolds / (@nNumberOfItems * 1.0) ELSE NULL END;

DELETE FROM @t
WHERE ItemCount <> 0 AND 
      (@fRatio IS NULL AND HoldCount / ItemCount < @nNumberOfHolds OR
       @fRatio IS NOT NULL AND HoldCount / (ItemCount * 1.0) < @fRatio);

-- Pre-calculate aggregated values
WITH AggregatedData AS (
    SELECT 
        CIR.AssociatedBibRecordID,
        MAX(CIR.AssignedCollectionID) AS MaxAssignedCollectionID,
        MAX(CIR.MaterialTypeID) AS MaxMaterialTypeID
    FROM 
        Polaris.Polaris.CircItemRecords CIR WITH (NOLOCK)
    GROUP BY 
        CIR.AssociatedBibRecordID
)

-- Update the table with aggregated data
UPDATE @t
SET 
    Author = Polaris.Polaris.Circ_GetSysHoldRequestDisplayAuthor(SHR.SysHoldRequestID, 0),
    Title = Polaris.Polaris.Circ_GetSysHoldRequestDisplayTitle(SHR.SysHoldRequestID, 0),
    ISBN = COALESCE(SHR.ISBN, Polaris.Polaris.Cat_GetBibRecordUPC(SHR.BibliographicRecordID, 0)),
    MARCTypeOfMaterial = MTOM.Description,
    CallNumber = BR.BrowseCallNo,
    HoldToItemRatio = CASE WHEN ItemCount > 0 THEN HoldCount * 1.0 / ItemCount ELSE NULL END,
    AssignedCollectionID = AD.MaxAssignedCollectionID,
    MaterialTypeID = AD.MaxMaterialTypeID
FROM @t t
INNER JOIN Polaris.Polaris.SysHoldRequests SHR WITH (NOLOCK) ON t.BibliographicRecordID = SHR.BibliographicRecordID
INNER JOIN Polaris.Polaris.BibliographicRecords BR WITH (NOLOCK) ON SHR.BibliographicRecordID = BR.BibliographicRecordID
LEFT OUTER JOIN Polaris.Polaris.MARCTypeOfMaterial MTOM WITH (NOLOCK) ON BR.PrimaryMARCTOMID = MTOM.MARCTypeOfMaterialID
LEFT OUTER JOIN AggregatedData AD ON t.BibliographicRecordID = AD.AssociatedBibRecordID;

-- Display the final results
SELECT 
    t.BibliographicRecordID,
    t.Author,
    t.Title,
    t.ISBN,
    t.MARCTypeOfMaterial,
    t.HoldCount,
    t.ItemCount,
    t.CallNumber,
    t.HoldToItemRatio,
	mt.Description,
	c.Name
FROM @t t
join polaris.polaris.MaterialTypes mt on mt.MaterialTypeID = t.MaterialTypeID
join polaris.polaris.Collections c on c.CollectionID = t.AssignedCollectionID
WHERE t.HoldCount >= @nNumberOfHolds AND t.ItemCount >= @nNumberofItems
ORDER BY t.HoldCount DESC, t.Title;