Useful Code snippets

Code examples useful for query construction

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)

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"