Useful Code snippets

Code examples useful for query construction

Aggregate multi fields

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

Concatenate multiple fields

patron_record_fullname.first_name || ' ' || patron_record_fullname.middle_name || ' ' || patron_record_fullname.last_name AS Name

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 id to record number

id2reckey(patron_view.id)||'a' AS pnumber

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 

Location codes belong to a location

location_code LIKE 'nee%'

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"

Relative date limit

WHERE
item_view.record_creation_date_gmt < (localtimestamp - interval '60 days')

Relative total

round(cast(count (*) as numeric (12,2)) / ((select cast(count (*)as numeric (12,2)) from sierra_view.item_view where location_code LIKE 'nee%')), 6) as relative_item_total

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"

Unavailable Items

HAVING
  SUM(
    CASE
      WHEN s2_i.item_status_code = '$' THEN 0 -- Lost and Paid
      WHEN s2_i.item_status_code = 'd' THEN 0 -- Damaged
      WHEN s2_i.item_status_code = 'e' THEN 0 -- e-resource
      WHEN s2_i.item_status_code = 'm' THEN 0 -- Missing
      WHEN s2_i.item_status_code = 'n' THEN 0 -- Billed
      WHEN s2_i.item_status_code = 'o' THEN 0 -- Library Use Only
      WHEN s2_i.item_status_code = 'w' THEN 0 -- Withdrawn
      WHEN s2_i.item_status_code = 'z' THEN 0 -- Claims Returned
      ELSE 1
    END
  ) = 0 -- The number of available items is 0