Greetings!
My questions are: Does the SQL below look correct for providing a data basis for analyzing Turnover Rate during defined time periods (Start Date, End Date)?
Do any Polaris power users have existing preferences for analyzing turnover rates over time?
My experience has shown that the stock ‘Turnover Rate’ report in Polaris takes Start and End date parameters into account when summing the checkouts over the duration, but the item count portion of the turnover rate calculation is only the static snapshot of items at that moment the report is run!
I’ve been advised that determining # of items within a given past time frame would be difficult and have turned to a method for capturing ItemCount (daily, by branch and collection) moving forward.
This should generate a table where one can sum the checkouts over the period while averaging the item count over the same period.
My next step is to create a SQL Server Job and run this on a schedule but the execution script is outputting both the desired table but also a ‘Return Value’ of 0. I am still a relative SQL newbie!
Thanks in advance for your consideration. I’ve found this forum so kind and helpful. I wish I could spend a little more time here.
WITH CircCountQuery AS (
SELECT
CONVERT(DATE, th.TranClientDate) AS Date,
th.OrganizationID AS AssignedBranchID,
td.numValue AS AssignedCollectionID,
COUNT(DISTINCT th.transactionID) AS CircCount
FROM
PolarisTransactions.Polaris.TransactionHeaders th (NOLOCK)
INNER JOIN
PolarisTransactions.Polaris.TransactionDetails td (NOLOCK) ON th.TransactionID = td.TransactionID AND td.TransactionSubTypeID = 61 --assigned coll. code
WHERE
th.TransactionTypeID = 6001
AND th.OrganizationID IN (29,30,31,32,33,34)
AND th.TranClientDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0) -- Set the start of the previous day
AND th.TranClientDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) -- Set the start of the current day
AND td.numValue IS NOT NULL
GROUP BY
th.OrganizationID, td.numValue, CONVERT(DATE, th.TranClientDate)
),
ItemCountQuery AS (
SELECT
cir.AssignedBranchID,
cir.AssignedCollectionID,
COUNT(DISTINCT cir.itemrecordID) AS ItemCount
FROM
Polaris.Polaris.CircItemRecords cir(NOLOCK)
WHERE
cir.AssignedBranchID IN (29,30,31,32,33,34)
--AND cir.ItemStatusID IN (/* specify your desired ItemStatusID values here */)
GROUP BY
cir.AssignedBranchID, cir.AssignedCollectionID
)
-- Combine the results using LEFT JOIN
SELECT
COALESCE(CircCountQuery.Date, '1900-01-01') AS Date,
COALESCE(CircCountQuery.AssignedBranchID, ItemCountQuery.AssignedBranchID) AS AssignedBranchID,
COALESCE(CircCountQuery.AssignedCollectionID, ItemCountQuery.AssignedCollectionID) AS AssignedCollectionID,
COALESCE(CircCountQuery.CircCount, 0) AS CircCount,
COALESCE(ItemCountQuery.ItemCount, 0) AS ItemCount
FROM
CircCountQuery
LEFT JOIN
ItemCountQuery ON CircCountQuery.AssignedBranchID = ItemCountQuery.AssignedBranchID
AND CircCountQuery.AssignedCollectionID = ItemCountQuery.AssignedCollectionID;
ere