Aggregated Hourly Circ Counts by Day of the Week

Clearinghouse Repository Polaris Repository
,
1

If you need to know your busiest circulation hours for the week, then this query will help. It pulls circulation data for a set time period and then breaks that down for every hour of every day of the week, aggregated over the time period. So if you run this query for a two month time span, you’ll find out what your circ was like on given hours, on given days, over that timespan.

SELECT
    o.Name AS [Branch / Library],
    DATENAME(dw, th.TranClientDate) AS [Day],
    CASE 
        WHEN DATEPART(hh,th.TranClientDate) = 0 THEN '12AM-1AM'
        WHEN DATEPART(hh,th.TranClientDate) = 1 THEN '1AM-2AM' 
        WHEN DATEPART(hh,th.TranClientDate) = 2 THEN '2AM-3AM' 
        WHEN DATEPART(hh,th.TranClientDate) = 3 THEN '3AM-4AM' 
        WHEN DATEPART(hh,th.TranClientDate) = 4 THEN '4AM-5AM' 
        WHEN DATEPART(hh,th.TranClientDate) = 5 THEN '5AM-6AM' 
        WHEN DATEPART(hh,th.TranClientDate) = 6 THEN '6AM-7AM' 
        WHEN DATEPART(hh,th.TranClientDate) = 7 THEN '7AM-8AM' 
        WHEN DATEPART(hh,th.TranClientDate) = 8 THEN '8AM-9AM' 
        WHEN DATEPART(hh,th.TranClientDate) = 9 THEN '9AM-10AM' 
        WHEN DATEPART(hh,th.TranClientDate) = 10 THEN '10AM-11AM'
        WHEN DATEPART(hh,th.TranClientDate) = 11 THEN '11AM-12PM' 
        WHEN DATEPART(hh,th.TranClientDate) = 12 THEN '12PM-1PM'
        WHEN DATEPART(hh,th.TranClientDate) = 13 THEN '1PM-2PM'
        WHEN DATEPART(hh,th.TranClientDate) = 14 THEN '2PM-3PM'
        WHEN DATEPART(hh,th.TranClientDate) = 15 THEN '3PM-4PM' 
        WHEN DATEPART(hh,th.TranClientDate) = 16 THEN '4PM-5PM' 
        WHEN DATEPART(hh,th.TranClientDate) = 17 THEN '5PM-6PM' 
        WHEN DATEPART(hh,th.TranClientDate) = 18 THEN '6PM-7PM' 
        WHEN DATEPART(hh,th.TranClientDate) = 19 THEN '7PM-8PM' 
        WHEN DATEPART(hh,th.TranClientDate) = 20 THEN '8PM-9PM'
        WHEN DATEPART(hh,th.TranClientDate) = 21 THEN '9PM-10PM'
        WHEN DATEPART(hh,th.TranClientDate) = 22 THEN '10PM-11PM'
        WHEN DATEPART(hh,th.TranClientDate) = 23 THEN '11PM-12AM' 
    END AS [Hours],
    COUNT(DISTINCT th.transactionid) AS [Total Circ]

FROM
    PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)

INNER JOIN -- Get the organization names
    Polaris.Polaris.Organizations o (nolock) 
    ON (th.OrganizationID = o.OrganizationID)

WHERE -- Checkouts
    th.TransactionTypeID = 6001
AND -- Adjust dates as needed
    th.TranClientDate BETWEEN '2023-06-01 00:00:00' AND '2023-08-31 23:59:59'
AND -- Adjust organizations as needed
    th.OrganizationID IN (2,3,4)

GROUP BY
    o.Name,
    DATENAME(dw, th.TranClientDate),
    DATEPART(hh,th.TranClientDate)
ORDER BY
    DATENAME(dw, th.TranClientDate)