Static SQL Queries & Reports
The following queries are useful for reporting purposes
Collection Value
click to run Collection Value query on current_collection database
with branch_locations as (
select
n.name,
b.code_num,
l.*
from
branch_name as n
join branch as b on b.id = n.branch_id
join location as l on l.branch_code_num = b.code_num
)
select
l.name as branch_name,
item_format,
sum(price_cents) / 100.0 as total_value
from
branch_locations as l
join
item as i on i.location_code = l.code
group by
l.name,
i.item_format
Available Items & Circulation Information By Location at Branch
For item status -, aggregate count of total items, items with 0 checkouts, items with 1 or more checkouts, and items checked out at the time of the snapshot.
Note: This query accepts the query parameter, branch_code_num. These codes for CHPL Branch locations can be found from the following query: branch names and code numbers
click to run query on current_collection database
select
i.location_code,
ln.name,
-- loc.branch_code_num,
-- bn.name as branch_name,
count(*) as count_total_available_items,
(
select
count(*)
from
item as i2
where
i2.location_code = i.location_code
and i2.item_status_code = '-'
and i2.checkout_total = 0
) as count_items_0_checkouts,
(
select
count(*)
from
item as i2
where
i2.location_code = i.location_code
and i2.item_status_code = '-'
and i2.checkout_total > 0
) as count_items_gt_0_checkouts,
(
select
count(*)
from
item as i2
where
i2.location_code = i.location_code
and i2.item_status_code = '-'
and i2.checkout_date is not null
) as count_curr_checked_out
from
item as i
left outer join location as loc on loc.code = i.location_code
left outer join location_name as ln on ln.location_id = loc.id
left outer join branch as br on br.code_num = loc.branch_code_num
left outer join branch_name as bn on bn.branch_id = br.id
where
i.item_status_code = '-'
and br.code_num = :branch_code_num
group by
i.location_code,
ln.name
order by
loc.branch_code_num
Lucky Day Leased Books and Leased DVDs Analysis
click to run query on current_collection database
This report will produce a simple analysis of the Lucky Day Items (identified by items with the item format (‘Leased Book’, ‘Leased DVD’) and item barcodes starting with the character l). The report is Title-based, and compiles the average age in days of linked items, total counts of linked items, total checkouts linked items, and a cost per item checkout (based on the item price).
-- find lucky day leased books and leased dvds, and provide some basic statistics around those items grouped by title
with ld_item_info as (
select
item.bib_record_num,
price_cents,
item.checkout_total,
-- lucky day items are not renewable
-- item.renewal_total,
item.item_status_code,
item.creation_date,
item.barcode,
item.item_format
from
item
where
item.item_format in ('Leased Book', 'Leased DVD')
and lower(item.barcode) LIKE "l%"
)
select
bib.best_title,
bib.bib_record_num,
bib.creation_date as bib_creation_date,
(
select
COUNT(*)
from
item
where
item.bib_record_num = bib.bib_record_num
and item.item_format not in ('Leased Book', 'Leased DVD')
limit
1
) as count_non_ld_items,
(
select
sum(checkout_total)
from
item
where
item.bib_record_num = bib.bib_record_num
and item.item_format not in ('Leased Book', 'Leased DVD')
limit
1
) as total_non_ld_items_checkouts,
ld.item_format as ld_item_format,
round(
avg(
(julianday('now') - julianday(ld.creation_date))
),
1
) as avg_ld_item_age_days,
count(*) as count_ld_items,
sum(checkout_total) as total_ld_items_checkouts,
sum(price_cents) / 100.0 as total_ld_items_price,
round(
(sum(price_cents) / 100.0) / sum(checkout_total),
2
) as cost_per_ld_checkout
from
ld_item_info as ld
join bib on bib.bib_record_num = ld.bib_record_num
group by
bib.best_title,
bib.bib_record_num,
bib.creation_date,
ld.item_format
order by
avg_ld_item_age_days
New Books List
This query can be modified to include new items by a supplied item_type value.
It’s also possible to modify the query to include information about the intended audience from the location code data
New Titles by Item Type …
with item_data as (
with date_data as (
select
-- consider a 1 month period of time ...
-- start of last week ... advance to next monday, subtract 5 weeks
date('now', 'weekday 1', '-35 days') as start_date
)
select
item.item_format,
-- TODO maybe consider audience here from the location code
-- pad the code so we can examine the parts later ...
case
when length(item.location_code) = 5 then item.location_code
when length(item.location_code) = 4 then ' ' || item.location_code
when length(item.location_code) = 3 then ' ' || item.location_code
when length(item.location_code) = 2 then ' ' || item.location_code
when length(item.location_code) = 1 then ' ' || item.location_code
else null
end as location_code,
item.item_record_num,
item.bib_record_num,
bib_record.cataloging_date_gmt
from
date_data,
item
join bib_record_item_record_link as l on l.item_record_num = item.item_record_num
join bib_record on bib_record.record_id = l.bib_record_id
where
item.item_format = :item_format
and bib_record.cataloging_date_gmt >= date_data.start_date
)
select
item_data.item_format,
item_data.bib_record_num,
bib.best_author,
bib.best_title,
bib.publish_year,
count(item_data.item_record_num) as count_items,
'https://cincinnatilibrary.bibliocommons.com/v2/record/S170C' || coalesce(item_data.bib_record_num, '') as catalog_link -- this was the previous way to create links ..
-- 'https://cincinnatilibrary.bibliocommons.com/item/show/' || coalesce(item_data.bib_record_num, '') || '170' as catalog_link -- , bib.*
from
item_data
join bib on bib.bib_record_num = item_data.bib_record_num
group by
item_data.bib_record_num
order by
bib.best_title
Items with 0 Circulation by branch_name (including pagination)
-- items with 0 checkouts by given branch_name
with item_data as (
with locations as (
select
"location".code as location_code,
"location_name".name as location_name,
"branch_name".name as branch_name
from
"location"
join "location_name" on "location_name".location_id = "location".id
join "branch" on "branch".code_num = "location".branch_code_num
join "branch_name" on "branch_name".branch_id = "branch".id
where
"branch_name".name = :branch_name -- and "location".code = : location_code
)
select
ROW_NUMBER() over (
order by
item.location_code,
item.item_callnumber
) as row_num,
item.item_record_num,
item.bib_record_num,
item.location_code,
locations.location_name as location_name,
item.item_callnumber,
item.item_format,
item.creation_date as item_creation_date,
cast (
round(
(
julianday(date('now')) - julianday(date(item.creation_date))
),
0
) as integer
) as item_age_days,
item.record_last_updated as item_last_updated,
item.price_cents
from
locations
join item on item.location_code = locations.location_code
where
-- consider these status codes as availbale
item.item_status_code in (
'-',
'!',
'b',
'p',
'(',
'@',
')',
'_',
'=',
'+',
't'
)
and item.checkout_total = 0
order by
row_num
)
select
row_num,
-- (
-- select
-- max(row_num)
-- from
-- item_data
-- ) as total_row_num,
item_data.item_record_num,
item_data.bib_record_num,
item_data.location_code,
item_data.location_name,
item_data.item_age_days,
item_data.item_format,
item_data.item_callnumber,
bib.best_author,
bib.best_title,
bib.publish_year,
bib.isbn,
item_data.item_creation_date,
item_data.item_last_updated,
item_data.price_cents
from
item_data
join bib on bib.bib_record_num = item_data.bib_record_num
order by
row_num
limit
3000 offset (:page_num_from_zero * 3000)
Item Data Consistency Report – Excluded Titles
click to run query on current_collection database
-- this query will display some bib and item information for titles that are to be excluded from the Item Data Consistency Report
select
b.bib_record_num,
b.best_author,
b.best_title,
cast(publish_year as integer) as publish_year,
b.creation_date,
b.record_last_updated,
b.isbn,
(
select
count(*)
from
item
where
item.bib_record_num = b.bib_record_num
) as total_item_count,
(
with locations as (
select
DISTINCT location_code
from
item
where
item.bib_record_num = b.bib_record_num
order by
location_code
)
select
group_concat (location_code)
from
locations
) as item_locations
from
bib as b
where
-- these titles are considered "teen classics" or otherwise, and are excluded from the IDC report
bib_record_num in (
1008088,
1008092,
1008324,
1009074,
1012471,
1012960,
1016931,
1023324,
1025647,
1026944,
1030135,
1032779,
1033764,
1035984,
1036364,
1038132,
1041785,
1042130,
1044943,
1045391,
1057164,
1068843,
1069142,
1080942,
1098072,
1123311,
1125257,
1131252,
1136783,
1137858,
1149649,
1156722,
1163065,
1195037,
1198983,
1208160,
1208782,
1214946,
1258923,
1260206,
1262052,
1262195,
1263884,
1268373,
1268384,
1274970,
1276299,
1283114,
1285037,
1318751,
1321722,
1328024,
1330867,
1332284,
1375132,
1376771,
1386082,
1392809,
1395441,
1405850,
1417890,
1422875,
1427726,
1465219,
1465868,
1473691,
1476334,
1482199,
1500156,
1500725,
1519112,
1519118,
1520620,
1521555,
1523209,
1524032,
1524039,
1524049,
1528683,
1534705,
1542739,
1555182,
1557339,
1557775,
1564639,
1573242,
1579598,
1584994,
1596027,
1610988,
1630040,
1637976,
1639082,
1639351,
1657016,
1657539,
1723544,
1732910,
1748806,
1750917,
1751512,
1753059,
1756363,
1765488,
1777013,
1777554,
1789689,
1798623,
1806397,
1815906,
1821901,
1823479,
1824853,
1824863,
1824881,
1837580,
1874105,
1874105,
1874617,
1881635,
1891612,
1893725,
1900878,
1915536,
1933582,
1934753,
1960352,
1961576,
1961887,
1967302,
1986993,
1992305,
1996454,
2005510,
2006956,
2006985,
2008273,
2012712,
2014369,
2028943,
2040871,
2048799,
2052473,
2069758,
2070459,
2080910,
2081561,
2086313,
2089850,
2092147,
2092155,
2111249,
2118284,
2130304,
2133134,
2137975,
2169420,
2171086,
2186599,
2203330,
2203330,
2203367,
2204141,
2210745,
2212066,
2215585,
2220611,
2225085,
2228373,
2229190,
2229649,
2247002,
2506864,
2252851,
2264431,
2265447,
2268806,
2270361,
2315417,
2325236,
2330280,
2331675,
2349894,
2377225,
2385659,
2388695,
2390408,
2399213,
2401846,
2402050,
2403296,
2424769,
2427365,
2439149,
2449995,
2454966,
2460026,
2467038,
2476394,
2476870,
2487394,
2492541,
2493883,
2494668,
2508710,
2518435,
2526514,
2530079,
2530507,
2532883,
2538123,
2540289,
2540405,
2547935,
2556742,
2560158,
2566314,
2572417,
2574892,
2578161,
2592633,
2598018,
2610287,
2610368,
2611069,
2611525,
2613714,
2615465,
2615487,
2615515,
2615605,
2615620,
2615705,
2615908,
2619886,
2624870,
2628120,
2628125,
2638970,
2640657,
2643029,
2654111,
2659891,
2663126,
2667577,
2670636,
2670823,
2676813,
2693063,
2697347,
2702313,
2712108,
2712532,
2712549,
2712608,
2713686,
2713850,
2726440,
2729046,
2738268,
2739884,
2741117,
2772166,
2784353,
2784616,
2785618,
2788500,
2792223,
2792790,
2823065,
2883551,
2886553,
2963099,
2969363,
2972940,
2994736,
3134360,
3192709,
3193734,
3202674,
3285022,
3293824,
1416907,
2493664,
2985934,
2985935,
2493587,
1803522,
2755125,
2714814,
2500300,
2985933,
3108309,
3108308,
2884705,
2275400,
3229667,
1803502,
1803512,
2275489,
2985932,
2981982,
1832463,
1971745,
1770999,
2318436,
2096954,
3181534,
3181535,
3181536,
3245632,
2970259,
3204670,
3509035,
3208365,
2662378,
3383599,
3371597,
3383599,
2247002,
3371597,
2506864,
1906584,
2750249,
2410509,
742749,
3059271,
2508695,
3352422,
3150089,
2555245,
3208436,
2884408,
2786980,
2544222,
2686721,
3287286,
2987092,
3238720,
3393392,
3466158,
2599355,
1579122,
2771545
)
order by
creation_date
Titles with 100% items (active) checked out (by popularity–total items checkouts)
click to run query on current_collection database
-- titles with 100% active items checked out (by popularity--total items checkouts)
with titles_with_active_items as (
-- titles with active items
select
bib.bib_record_num,
count(item.item_record_num) as total_items,
count(item.due_date) as checked_out,
sum(item.checkout_total) as sum_items_checkouts,
min(item.due_date) as min_due_date
from
bib
join item on item.bib_record_num = bib.bib_record_num
where
-- these are the status codes we want to consider for items as being "active"
-- ... for a list of the status codes, and the descriptive names, see the following link
-- https://ilsweb.cincinnatilibrary.org/collection-analysis/current_collection/item_status_property_myuser
item.item_status_code in ('-', '!', 'b', 'p', '(', '@', ')', '_', '=', '+')
group by
bib.bib_record_num
)
select
t.*,
bib.best_title,
bib.best_author,
bib.publish_year,
bib.bib_level_callnumber,
bib.creation_date
from
titles_with_active_items as t
join bib on bib.bib_record_num = t.bib_record_num
where
t.total_items = t.checked_out
order by
sum_items_checkouts DESC
limit
3000 offset (:page) * 3000
Percent Total Items Checked Out by Location at Branch (given branch code)
click to run query on current_collection database
with data as (
select
i.location_code,
ln.name,
-- loc.branch_code_num,
-- bn.name as branch_name,
count(*) as count_total_available_items,
(
select
count(*)
from
item as i2
where
i2.location_code = i.location_code
and i2.item_status_code = '-'
and i2.checkout_total = 0
) as count_items_0_checkouts,
(
select
count(*)
from
item as i2
where
i2.location_code = i.location_code
and i2.item_status_code = '-'
and i2.checkout_total > 0
) as count_items_gt_0_checkouts,
(
select
count(*)
from
item as i2
where
i2.location_code = i.location_code
and i2.item_status_code = '-'
and i2.checkout_date is not null
) as count_curr_checked_out
from
item as i
left outer join location as loc on loc.code = i.location_code
left outer join location_name as ln on ln.location_id = loc.id
left outer join branch as br on br.code_num = loc.branch_code_num
left outer join branch_name as bn on bn.branch_id = br.id
where
i.item_status_code = '-'
and br.code_num = :branch_code_num
group by
i.location_code,
ln.name -- loc.branch_code_num,
-- branch_name
order by
loc.branch_code_num
)
select
*,
round(
(
(data.count_curr_checked_out * 1.0) / (data.count_total_available_items * 1.0)
) * 100.0,
2
) as pct_tot_curr_checked_out
from
data