Question: Is there any way to get specific item usage by hour? (From Innovative ILS)
You can get hourly checkout information through a SQL
query assuming that your circ_trans view is being populated with data (I'm not
sure if that is done by default for everyone yet), the period of time that is
covered by your circ_trans data, and the period you want to investigate.
Here is a simple example that will give an hourly
breakdown of all checkouts for yesterday:
SELECT
TO_CHAR(ct.transaction_gmt, 'HH24:00') AS "Hour",
COUNT(*)
AS "Checkouts"
FROM
sierra_view.circ_trans AS ct
WHERE
ct.op_code =
'o'
-- checkout
AND
DATE(ct.transaction_gmt) = DATE(now() - INTERVAL '1 day') -- yesterday
GROUP BY 1
ORDER BY 1
;
This example adds a column for location code and limits
the output to items located in Langara's media dept:
SELECT
ct.item_location_code AS
"Location",
TO_CHAR(ct.transaction_gmt, 'HH24:00') AS "Hour",
COUNT(*)
AS "Checkouts"
FROM
sierra_view.circ_trans AS ct
WHERE
ct.op_code =
'o' -- checkout
AND
DATE(ct.transaction_gmt) = DATE(now() - INTERVAL '1 day') -- yesterday
AND
ct.item_location_code LIKE 'lm%' -- from Langara
Media
GROUP BY 1,2
ORDER BY 1,2
;
This example limits the query from Example 1 to a
specific item record number. This may be closest to what you're aiming for:
SELECT
TO_CHAR(ct.transaction_gmt, 'HH24:00') AS "Hour",
COUNT(*) AS "Checkouts"
FROM
sierra_view.circ_trans AS ct
JOIN
sierra_view.item_view AS iv
ON
iv.id =
ct.item_record_id
WHERE
ct.op_code =
'o'
-- checkout
AND
DATE(ct.transaction_gmt) = DATE(now() - INTERVAL '1 day') -- yesterday
AND
iv.record_num =
'1211068'
-- specific item record
GROUP BY 1
ORDER BY 1
;
--
Brent Searle
No comments:
Post a Comment