Deleted Item Counts by Branch, Collection, and User

Clearinghouse Repository Polaris Repository
,
1

Deleted Item Counts by Branch, Collection, and User

So long as items are deleted through Polaris and not through a SQL query, this code will provide a count of items deleted by branch, collection, and user.

SELECT
    COUNT(th.TransactionID) AS [Items Deleted],
    pu.Name AS [User],
    o.DisplayName AS [Assigned Branch],
    c.Name AS [Collection]
FROM
    PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Gets the collection information
    PolarisTransactions.Polaris.TransactionDetails tdcol WITH (NOLOCK)
    ON (th.TransactionID = tdcol.TransactionID and tdcol.TransactionSubTypeID = 61)
INNER JOIN -- Gets the assigned branch information
    PolarisTransactions.Polaris.TransactionDetails tdab WITH (NOLOCK)
    ON (th.TransactionID = tdab.TransactionID and tdab.TransactionSubTypeID = 58)
LEFT JOIN
    Polaris.Polaris.Collections c WITH (NOLOCK)
    ON (c.CollectionID = tdcol.numValue)
INNER JOIN -- Pulls the staff member (Polaris user) info
    Polaris.Polaris.PolarisUsers pu WITH (NOLOCK)
    ON (pu.PolarisUserID = th.PolarisUserID)
INNER JOIN
    Polaris.Polaris.Organizations o WITH (NOLOCK)
    ON (o.OrganizationID = tdab.numValue)
WHERE -- 3007 = Item record deleted
    th.TransactionTypeID = 3007
AND
    th.TranClientDate BETWEEN '2021-07-01 00:00:00.000' AND '2022-06-30 23:59:59.999'
GROUP BY
    pu.name,
    o.DisplayName,
    c.Name
ORDER BY
    pu.Name,
    o.DisplayName,
    c.Name