Static SQL Queries & Reports

The following queries are useful for reporting purposes

Bib Records Deleted Within the Last Week

click to run Bib Records Deleted Within the Last Week query

-- This is a list of metadata from bib records that have been deleted in the previous week
-- NOTE: page numbers start at 0, and will produce a max of 3000 records until no more remain to populate a "page"
with deleted_bibs as (
select
    bp.bib_record_num
from
    collection_prev.bib as bp
    left outer join current_collection.bib as bc on bc.bib_record_num = bp.bib_record_num
where
    bc.bib_record_num is null
order by
    bp.bib_level_callnumber,
    bp.best_author,
    bp.best_title,
    bp.bib_record_num
)
select
db.bib_record_num,
(
    select
    deletion_date_gmt
    from
    collection_prev.record_metadata as p
    where
    p.record_num = db.bib_record_num
    and p.record_type_code = 'b'
    limit
    1
) as deletion_date,
b.*
from
deleted_bibs as db
join collection_prev.bib as b on b.bib_record_num = db.bib_record_num
limit
3000 offset :page * 3000

Item Records Deleted Within the Last Week

click to run Item Records Deleted Within the Last Week query

-- This is a list of metadata from item records that have been deleted in the previouse week
-- NOTE: page numbers start at 0, and will produce a max of 3000 records until no more remain to populate a "page"
with deleted_items as (
select
    ip.item_record_num,
    ip.bib_record_num,
    ip.location_code,
    ip.item_callnumber,
    ip.item_format,
    ip.last_checkout_date,
    ip.checkout_total,
    ip.item_status_code
from
    collection_prev.item as ip
    left outer join current_collection.item as ic on ic.item_record_num = ip.item_record_num
where
    ic.item_record_num is null
)
select
di.*,
b.best_author,
b.best_title,
b.publish_year,
b.bib_level_callnumber
from
deleted_items as di
left outer join collection_prev.bib as b on b.bib_record_num = di.bib_record_num
order by
di.location_code
limit
3000 offset :page * 3000

Collection Value

click to run Collection Value query on current_collection database

click to run Collection Value query on collection-2021-01-04 database

click to run Collection Value query on collection-2020-01-06 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 …

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

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