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)

Pull out Juv/YA Age Level from Location code

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