Is there a query that can get me monthly stats for In House Circ?

Polaris
1

The default report collects lifetime and year to date stats.

2

There is! Are you just looking for a query or for a report?

If you just want a query, here’s one I use for a report. I can share the RDL if you wanted to try had upload a full report to your SSRS.

SELECT o.Abbreviation AS [Library], ISNULL(c.Name,'None') AS [Collection], mt.Description AS [Material Type], ISNULL(sl.Description,'None') AS [Shelf Location], COUNT(*) AS [In House Use Count]
FROM PolarisTransactions.Polaris.TransactionHeaders AS [th] WITH (NOLOCK)
--material type
INNER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td0] WITH (NOLOCK)
ON th.TransactionID = td0.TransactionID AND td0.TransactionSubTypeID = '4'
INNER JOIN Polaris.Polaris.MaterialTypes AS [mt] WITH (NOLOCK)
ON td0.numValue = mt.MaterialTypeID
--collection code
LEFT OUTER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td1]
ON th.TransactionID = td1.TransactionID AND td1.TransactionSubTypeID = '61'
LEFT OUTER JOIN Polaris.Polaris.Collections AS [c] WITH (NOLOCK)
ON td1.numValue = c.CollectionID
--shelf location
LEFT OUTER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td4]
ON th.TransactionID = td4.TransactionID AND td4.TransactionSubTypeID = '296'
LEFT OUTER JOIN Polaris.Polaris.ShelfLocations AS [sl] WITH (NOLOCK)
ON td4.numValue = sl.ShelfLocationID AND th.OrganizationID = sl.OrganizationID
--check in type
INNER JOIN PolarisTransactions.Polaris.TransactionDetails AS [td6] WITH (NOLOCK)
ON th.TransactionID =  td6.TransactionID AND td6.TransactionSubTypeID = '128'
INNER JOIN Polaris.Polaris.Organizations AS [o] WITH (NOLOCK)
ON th.OrganizationID = o.OrganizationID
WHERE th.TransactionTypeID ='6002'
AND td6.numValue IN ('6','56')
AND th.TranClientDate BETWEEN @StartDate AND DATEADD(day,1,@EndDate)
AND th.OrganizationID IN (@OrgID)
GROUP BY o.Abbreviation, ISNULL(c.Name,'None'), mt.Description, ISNULL(sl.Description,'None')
3

Could you tell me what the transaction sub type IDs are doing? specifically 4, 61, and 296.

I don’t think I actually have those

4

Those are for MaterialType, Assigned Collection Code, and Shelf location, respectively. I don’t think that specific subcodes can be enabled/disabled, just their parent transaction. You can use this query, expecting a value of 1, to make sure you’ve got check-in transaction logging enabled:

select tt.SysAdminuseTransaction from PolarisTransactions.polaris.TransactionTypes tt where tt.TransactionTypeID = 6002

And this query just to make absolute sure the subtypes are working as expected, also looking for a value of 1 for each of the subtypes:

select tst.TransactionSubTypeID, tst.IsImplemented from PolarisTransactions.polaris.TransactionSubTypes tst where tst.TransactionSubTypeID in (4,61,296)

Did you not get back the info you were expecting from Trevor’s query?

5

I think I got what I was looking for from that. I just wasn’t sure what those all were for and if they were necessary for the script to run.