/*
 * List the 50 highest-circing titles YTD.
 *
 * Bob Gaydos <bgaydos@starklibrary.org>
 * December 14, 2023
 */
SELECT
    SUM(i.year_to_date_checkout_total) AS "YTD Circ",
    RIGHT(i.location_code, 3) AS "Shelving Loc",
    TRIM(COALESCE(c.content, '')) AS "Call Number",
    brp.best_title AS "Title",
    brp.best_author AS "Author"
FROM item_record i
INNER JOIN record_metadata rm
   ON rm.id = i.id
INNER JOIN bib_record_item_record_link bil
   ON bil.item_record_id = i.id
INNER JOIN bib_record_property brp
   ON brp.bib_record_id = bil.bib_record_id
LEFT OUTER JOIN subfield c
   ON c.record_id = i.id
  AND c.field_type_code = 'c'
  AND c.tag = 'a'
WHERE TRIM(COALESCE(rm.campus_code, '')) = ''                                                -- exclude INN-Reach circ
  AND i.itype_code_num IN (2, 5, 6, 11, 13, 16, 18, 19, 21, 22, 23, 26, 32, 35, 37, 41, 44)  -- print items
  AND (i.location_code LIKE '__a__' OR i.location_code LIKE '__x__')                         -- adult or all ages
--AND i.location_code LIKE '__c__'                                                           -- child
--AND i.location_code LIKE '__t__'                                                           -- teen
GROUP BY
    RIGHT(i.location_code, 3),
    TRIM(COALESCE(c.content, '')),
    brp.best_title,
    brp.best_author
ORDER BY
    "YTD Circ" DESC,
    "Title",
    "Call Number",
    "Shelving Loc"
LIMIT 50
;