Static SQL Queries & Reports ============================ The following queries are useful for reporting purposes .. contents:: Collection Value ---------------- `click to run Collection Value query on current_collection database `_ .. code-block:: sql 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 `__ .. code-block:: sql 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). .. code-block:: sql -- 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 ... `New Release DVDs `__ `Book `__ `Juvenile Book `__ `Teen Book `__ `Reference Book `__ `DVD/Videocassette `__ `Music on CD `__ `Large Print Book `__ `Book on CD `__ `Music Score `__ `LP Record `__ `Juvenile Book on CD `__ `Playaway `__ `Juvenile Music on CD `__ .. code-block:: sql 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) -------------------------------------------------------------- .. code-block:: sql -- 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 `_ .. code-block:: sql -- 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 `__ .. code-block:: sql -- 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 `__ .. code-block:: sql 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