Useful Code snippets

Code examples useful for query construction

Aggregate multi fields

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

Author in firstname, lastname order

REPLACE(SPLIT_PART(SPLIT_PART(b.best_author,' (',1),', ',2),'.','')||' '||SPLIT_PART(b.best_author,', ',1)

Calculate check digit

SELECT
md.record_type_code||md.record_num||
COALESCE(
    CAST(
        NULLIF(
        (
            ( md.record_num % 10 ) * 2 +
            ( md.record_num / 10 % 10 ) * 3 +
            ( md.record_num / 100 % 10 ) * 4 +
            ( md.record_num / 1000 % 10 ) * 5 +
            ( md.record_num / 10000 % 10 ) * 6 +
            ( md.record_num / 100000 % 10 ) * 7 +
            ( md.record_num / 1000000 ) * 8
         ) % 11,
         10
         )
  AS CHAR(1)
  ),
  'x'
 ) AS "Bib Number"

Call number range table

 CASE
 WHEN varfield.field_content ~ '\|a0[0-9][0-9]' THEN '000'
 WHEN varfield.field_content ~ '\|a1[0-9][0-9]' THEN '100'
 WHEN varfield.field_content ~ '\|a2[0-9][0-9]' THEN '200'
 WHEN varfield.field_content ~ '\|a3[0-9][0-9]' THEN '300'
 WHEN varfield.field_content ~ '\|a4[0-9][0-9]' THEN '400'
 WHEN varfield.field_content ~ '\|a5[0-9][0-9]' THEN '500'
 WHEN varfield.field_content ~ '\|a6[0-9][0-9]' THEN '600'
 WHEN varfield.field_content ~ '\|a7[0-9][0-9]' THEN '700'
 WHEN varfield.field_content ~ '\|a8[0-9][0-9]' THEN '800'
 WHEN varfield.field_content ~ '\|a9[0-9][0-9]' THEN '900'
 ELSE 'unknown'
 END AS "Call#_Range",

Call number sort by department

ORDER BY
CASE
WHEN SUBSTRING(i.location_code FROM 4 FOR 1) = 'j' THEN 2
WHEN SUBSTRING(i.location_code FROM 4 FOR 1) = 'y' THEN 3
ELSE 1
END,
ip.call_number

Concatenate multiple fields account for null values

patron_record_fullname.first_name || ' ' || COALESCE(patron_record_fullname.middle_name, '') || ' ' || 
patron_record_fullname.last_name AS Name

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)

Crosstab by hour

SELECT
m.creation_date_gmt::DATE,
COUNT(p.id) FILTER (WHERE EXTRACT(HOUR from m.creation_date_gmt) = 15) AS "3pm",
COUNT(p.id) FILTER (WHERE EXTRACT(HOUR from m.creation_date_gmt) = 16) AS "4pm",
COUNT(p.id) FILTER (WHERE (EXTRACT(HOUR from m.creation_date_gmt) = 17 AND EXTRACT(MINUTE FROM M.creation_date_gmt) < 30)) AS "5pm",
COUNT(p.id) FILTER (WHERE (EXTRACT(HOUR from m.creation_date_gmt) = 17 AND EXTRACT(MINUTE FROM M.creation_date_gmt) >= 30)) AS "530pm",

Divide by Zero error fix

(COUNT(i.id)/(NULLIF(SUM(i.checkout_total),0)))

Encore URL

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

Filter calculations

AVG(i.price) FILTER(WHERE i.price>'0')

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%'

MD5 Hash Patron IDs

md5(CAST(c.patron_record_id AS varchar))

Mode

MODE() WITHIN GROUP (order by i.price)

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

Replace fixed field codes with labels

SELECT
p.name
FROM
sierra_view.item_record i
JOIN
sierra_view.itype_property_myuser p
ON i.itype_code_num = p.code

Reverse to extract last characters from a string

REVERSE(SUBSTRING(REVERSE(v.field_content) FOR 5))

Review file query

FROM
sierra_view.bib_view as b 
JOIN sierra_view.bool_set as sb ON b.id = sb.record_metadata_id AND sb.bool_info_id = '[review file #]'

Row numbers

SELECT
ROW_NUMBER() OVER (ORDER BY bib_record_property.best_title DESC) AS row_num,
bib_record_property.best_title AS title,
COUNT(bib_record_item_record_link.item_record_id) AS item_count

split_part to parse strings

SPLIT_PART(v.field_content, ' ', 9)
-- Takes 9th segment of field_content delimited by a space

Temp Table

DROP TABLE IF EXISTS mvhdholds;
CREATE TEMP TABLE mvhdholds AS
SELECT *
FROM Sierra_view.item_view;

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

Variable fields

FROM sierra_view.item_view as item
LEFT OUTER JOIN sierra_view.varfield AS call_num
  ON item.id = call_num.record_id AND call_num.varfield_type_code = 'c'
LEFT OUTER JOIN sierra_view.varfield AS vol
  ON item.id = vol.record_id AND vol.varfield_type_code = 'v'