Useful Code snippets

Code examples useful for query construction

Aggregate multi fields

string_agg(distinct(bib_record_location.location_code), ',')        AS "location code"

Convert code to label

CASE
    WHEN hold.status = '0' THEN 'On hold'
    WHEN hold.status = 'b' THEN 'Bib hold ready for pickup'
    WHEN hold.status = 'j' THEN 'Volume hold ready for pickup'
    WHEN hold.status = 'i' THEN 'Item hold ready for pickup'
    WHEN hold.status = 't' THEN 'In transit to pickup location'
    ELSE 'unexpected code '||hld.status
END                                                     AS "Hold Status"

Convert integer to numeric for accounting arithmetic

round(cast (fund.appropriation-fund.expenditure as numeric (12,2))/100, 2) AS "CASH BALANCE"

Converting timestamps

date(order_date_gmt)
date_part('year', order_date_gmt)

Encore URL

'http://find.minlib.net/iii/encore/record/C__Rb'||bv.record_num   AS "URL"

Join attached record types together

FROM
    sierra_view.bib_view                              AS bv
JOIN
    sierra_view.bib_record_item_record_link           AS bilink
    ON
    bv.id = bilink.bib_record_id
    JOIN
    sierra_view.item_view                             AS iv
    ON
    bilink.item_record_id = iv.id 

Pull out Juv/YA Age Level from Location code

CASE
WHEN SUBSTRING(item_view.location_code,4,1)='j' THEN 'Juv'
WHEN SUBSTRING(item_view.location_code,4,1)='y' THEN 'YA'
Else 'Adult'
END AS "Age level"

Turnover rate

round(cast(SUM(item_view.year_to_date_checkout_total) as numeric (12,2))/cast(count (bib_record_item_record_link.id) as numeric (12,2)), 2) as "turnover"