Page History

--define a parameters Common Table Expression (subquery or CTE) so the location filter can be easily changed in one place
--enter a location code between the quote marks below ('law,ref' is just an example)
--if left blank, the query will get all locations except for 'serv,remo'

WITH parameters AS 
    (
    SELECT 
      'law,ref' AS location_code_filter
    )

--Select distinct rows to reduce duplicates that may result from the joins
--and bringing in data fields from holdings records (in this case)
SELECT DISTINCT
    ipd.instance_id,                     --Instance UUID from the physical description table
    ipd.instance_hrid,                   --Instance HRID from the physical description table
    instext.title,                       --Title from the instance extension table
    ll.location_name,                    --Human-readable location name
    he.call_number,                      --Call number from the holdings extension table
    ipd.physical_description,            --Raw physical description text
    CASE
        --Check whether the physical description contains a 1- to 4-digit number
        WHEN substring(ipd.physical_description, '\d{1,4}') IS NOT NULL
        --Check whether the description looks like it refers to pages or leaves
        AND (
            ipd.physical_description LIKE '%page%' 
            OR ipd.physical_description LIKE '%p.%' 
            OR ipd.physical_description LIKE '%leaves%' 
            OR ipd.physical_description LIKE '% l.%'
        )
        --If both conditions are true, extract the first 1- to 4-digit number
        --and cast it to an integer as the page count estimate
        THEN substring(ipd.physical_description, '\d{1,4}')::INT
        --If no usable page or leaf count is found, return null
        ELSE NULL
    END AS page_count_estimate,
    --ordinality shows the order of the physical description entry from the record 
    --in cases where there is more than one
    ipd.physical_description_ordinality  

--Start with the instance extension table to get title-level data
FROM folio_derived.instance_ext AS instext

    --Join to holdings to bring in call number and location links
    LEFT JOIN folio_derived.holdings_ext AS he 
        ON instext.instance_id = he.instance_id
    
    --Join to locations/libraries to get location names and location codes
    LEFT JOIN folio_derived.locations_libraries AS ll 
        ON he.permanent_location_id = ll.location_id
    
    --Join to instance physical descriptions to get the physical description text
    LEFT JOIN folio_derived.instance_physical_descriptions AS ipd 
        ON instext.instance_id = ipd.instance_id

--Apply the location filter
WHERE 
    CASE
        --If the location filter parameter is blank, return all locations except 'serv,remo'
        WHEN (SELECT location_code_filter FROM parameters) = ''
            THEN ll.location_code != 'serv,remo'
        --Otherwise, return only the specific location code entered in the parameters CTE
        ELSE ll.location_code = (SELECT location_code_filter FROM parameters)
    END
;