Non-Expired Cardholders

SELECT
	pr.ptype_code as ptype_code,
	ppn.description as ptype_description, 
	count(*) as count_patrons	
FROM
	sierra_view.record_metadata as rm 
	JOIN sierra_view.patron_record pr on (
		pr.record_id = rm.id
		and pr.ptype_code <= 51
	)
	JOIN sierra_view.ptype_property as pp on pp.value = pr.ptype_code 
	JOIN sierra_view.ptype_property_name as ppn on ppn.ptype_id = pp.id  
WHERE 
	rm.record_type_code = 'p'
	and rm.campus_code = ''
	and date(pr.expiration_date_gmt) > '2024-01-01' -- non-expired
GROUP BY 
	1,2
ORDER BY
	3 desc

Find counts of distinct patrons with checkouts by branch

select 
	bn."name" as branch_name,
	count(distinct ct.patron_record_id) as count_distinct_patrons
from 
	sierra_view.circ_trans as ct 
	join sierra_view.statistic_group as sg on sg.code_num = ct.stat_group_code_num 
	join sierra_view."location" as l on l.code = sg.location_code 
	join sierra_view.branch as b on b.code_num = l.branch_code_num 
	join sierra_view.branch_name bn on bn.branch_id = b.id 
where 
	ct.op_code = 'o' -- checkouts
	and ct.ptype_code::INTEGER < 196
group by 
	branch_name
order by 
	count_distinct_patrons desc

find counts of patrons created within the last year (2023-01-20)

SELECT 
	to_char(rm.creation_date_gmt, 'YYYY') as patron_created_year, 
	pr.ptype_code as ptype_code,
	ppn.description as ptype_description, 
	count(*) as count_patrons	
FROM
	sierra_view.record_metadata as rm 
	JOIN sierra_view.patron_record pr on (
		pr.record_id = rm.id
		and pr.ptype_code <= 51
	)
	JOIN sierra_view.ptype_property as pp on pp.value = pr.ptype_code 
	JOIN sierra_view.ptype_property_name as ppn on ppn.ptype_id = pp.id  
WHERE 
	rm.record_type_code = 'p'
	and rm.campus_code = ''
	and rm.creation_date_gmt >= '2022-01-01'
	and rm.creation_date_gmt < '2023-01-01'
	-- NOTE:
	-- We may want to include expired cards? 
	-- This would have the effect of increasing the count of "Online Registraion" cards
	--and pr.expiration_date_gmt::date >= now()::date 
GROUP BY 
	1,2,3
ORDER BY
	4 desc

Output Example:

patron_created_year	ptype_code	ptype_description	count_patrons
2022	0	Unlimited	21274
2022	51	Online Registration	11229
2022	1	Child Only (7 Items)	10106
2022	2	Teen Only (7 Items)	3252
2022	3	Adult Only (7 Items)	1688
2022	10	Educator	883
2022	7	Downloadables Only	859
2022	5	Limited Juvenile Material Only	491
2022	12	Outreach-NO Rating Sheet	206
2022	6	Limited No Videos	86
2022	15	Bookclub	64
2022	32	Non-Resident Unlimited	49
2022	41	Visitor Limited to Print Mat	16
2022	30	Non-Resident Juv Mat Only	2
2022	40	Visitor Juv Print Mat Only	1
2022	50	Complimentary	1

find patron record info for patrons who have names that have been entered in all caps

SELECT 
	pr.home_library_code ,
	pr.ptype_code ,
	rm.record_num ,
	(
		SELECT 
			json_agg(
				v.field_content ORDER BY v.occ_num 
			)
		FROM
			sierra_view.varfield AS v
		WHERE 
			v.record_id = prf.patron_record_id 
			AND v.varfield_type_code = 'b'	
	) AS patron_record_barcodes,
--	rm.id AS patron_record_id,
	date(rm.creation_date_gmt) AS patron_record_creation_date ,
	date(rm.record_last_updated_gmt) AS patron_record_last_update ,
	date(pr.expiration_date_gmt) AS patron_record_expiration_date ,
	prf.last_name ,
	prf.first_name ,
	prf.middle_name 
FROM 
	sierra_view.patron_record_fullname AS prf
	JOIN sierra_view.record_metadata AS rm ON (rm.id = prf.patron_record_id AND rm.campus_code = '') 
	JOIN sierra_view.patron_record AS pr ON pr.record_id = prf.patron_record_id
WHERE 
	(
		last_name ~ '^[A-Z0-9]{1,}(\s|$)'
		OR prf.first_name ~ '^[A-Z0-9]{2,}(\s|$)' -- CHANGE the {1,} to {2,} TO work with or without initials respectively 
	)
	AND pr.ptype_code :: INTEGER < 196
ORDER BY 
	1 ASC,
	2 ASC,
	5 DESC

report on what fines would be purged based on a 5 year span

SELECT
'p' || r.record_num || 'a' as patron_record_num,
-- removing barcode unless we really need it
-- (
-- 	SELECT
-- 	e.index_entry as barcode
-- 	from sierra_view.phrase_entry as e
-- 
-- 	WHERE
-- 	e.record_id = r.id
-- 	AND e.index_tag || e.varfield_type_code = 'bb'
-- 
-- 	ORDER BY
-- 	e.occurrence asc
-- 
-- 	LIMIT 1
-- ) as barcode,
p.owed_amt as current_owed_amt,
-- may or may not want to flag occurrences where the computed amount is different than the owed_amt on the patron record
-- (
-- 	SELECT
-- 	CASE 
-- 		WHEN SUM( (f.item_charge_amt + f.processing_fee_amt + billing_fee_amt) - f.paid_amt ) = p.owed_amt
-- 		THEN true
-- 
-- 		ELSE false
-- 	END
-- 
-- 	FROM
-- 	sierra_view.fine as f
-- 
-- 	WHERE
-- 	f.patron_record_id = p.record_id
-- 
-- 	GROUP BY
-- 	f.patron_record_id
-- ) AS computed_equal_current,
(
	SELECT
	SUM( (f.item_charge_amt + f.processing_fee_amt + billing_fee_amt) - f.paid_amt )
	FROM
	sierra_view.fine as f
	WHERE
	-- the date of the statute of limitations fines we're looking for
	f.assessed_gmt <= date_trunc('day', (NOW() - INTERVAL '5 years') )
	AND f.patron_record_id = p.record_id
	GROUP BY
	f.patron_record_id
) AS sum_prev_statue_lim_date,
-- compute the amount after the purge
(	
	p.owed_amt - (
		SELECT
		SUM( (f.item_charge_amt + f.processing_fee_amt + billing_fee_amt) - f.paid_amt )
		FROM
		sierra_view.fine as f
		WHERE
		-- the date of the statute of limitations fines we're looking for (5 years prior to today's date)
		f.assessed_gmt <= date_trunc('day', (NOW() - INTERVAL '5 years') )
		AND f.patron_record_id = p.record_id
		GROUP BY
		f.patron_record_id
	)
) as post_purge_owed_amt,
date_trunc('day',p.expiration_date_gmt)::date as expiration_date,
date_trunc('day',p.activity_gmt)::date as activity,
(
	SELECT
	date_trunc('day', f.assessed_gmt)::date
	FROM
	sierra_view.fine as f
	WHERE
	f.patron_record_id = p.record_id
	ORDER BY
	f.assessed_gmt ASC
	LIMIT
	1
) as first_assessed_fine_date
FROM
sierra_view.record_metadata as r
JOIN
sierra_view.patron_record as p
ON
  r.id = p.record_id
WHERE
r.deletion_date_gmt is null
AND r.campus_code = ''
-- AND p.expiration_date_gmt >= '2017-06-09'
AND r.id IN (
	SELECT
	f.patron_record_id
	FROM
	sierra_view.fine as f
	WHERE
	-- the date of the statute of limitations fines we're looking for
	f.assessed_gmt <= date_trunc('day', (NOW() - INTERVAL '5 years') )
	GROUP BY
	f.patron_record_id
)
ORDER BY
p.owed_amt,
first_assessed_fine_date

count number of patron selfcheck sessions (grouped by patron and timestamp truncated to hour and then counted by statistic_group)

WITH selfcheck_statgroups AS (
SELECT
*
FROM
sierra_view.statistic_group_myuser

where
name ~* '^.*schk[0-9]{1,}'

ORDER BY
location_code,
name
)

SELECT
s.*,
(
	WITH patron_hour_counts AS (
	SELECT
	date_trunc('hour', c.transaction_gmt) as hour,
	c.patron_record_id

	-- , COUNT(*)

	FROM
	sierra_view.circ_trans as c
	where
	c.stat_group_code_num = s.code

	GROUP BY
	hour,
	c.patron_record_id

	-- ORDER BY
	-- c.id
	)

	SELECT COUNT(*) FROM patron_hour_counts
)
FROM
selfcheck_statgroups as s

-- JOIN
-- sierra_view.circ_trans as c
-- ON
--   c.stat_group_code_num = s.code
-- 
-- 
-- LIMIT 100
;

find patron emails from a given list of barcodes:

DROP TABLE IF EXISTS temp_barcodes;
CREATE TEMP TABLE temp_barcodes (
	id SERIAL NOT NULL,
	barcode VARCHAR(300)
);

INSERT INTO temp_barcodes (barcode) VALUES
-- one way to generate these lines from a spreadsheet:
-- use this function (assuming the barcode is in column A:
-- =CONCATENATE("(","""","b",A1,"""","),")
-- and then fill that function down in the spreadsheet, 
-- and then simply cut and paste below:
('bSR123'),
('bSR124')
;
---

SELECT
t.*,
(
	SELECT
	v.field_content
	FROM
	sierra_view.varfield as v

	WHERE
	v.varfield_type_code = 'z'
	AND v.record_id = e.record_id

	ORDER BY
	v.occ_num

	LIMIT 1
) as first_listed_email

FROM
temp_barcodes as t

LEFT OUTER JOIN
sierra_view.phrase_entry as e
ON
  (e.index_tag || e.index_entry) = LOWER(t.barcode)
;

find under 18 patrons (at the time their record was created) having unlimited card types. Group them by home library location and count them.

SELECT
p.home_library_code,
COUNT(*)

FROM
sierra_view.patron_record as p

JOIN
sierra_view.record_metadata as r
ON
  r.id = p.record_id

WHERE
r.campus_code = ''
-- unlimited card type
AND p.ptype_code = 0
-- between February 2016 - April 2019
AND r.creation_date_gmt::date >= '2016-02-01'::date
AND r.creation_date_gmt::date < '2019-05-01'::date
-- at patron record creation date AGE is under 18 years
AND ( AGE(p.birth_date_gmt) - AGE(r.creation_date_gmt) ) < INTERVAL '18 years'

GROUP BY
p.home_library_code

Export for "Contactable by Foundation"

SELECT
-- COUNT(*)
r.record_type_code || r.record_num || 'a' AS "RECORD #(PATRON)",
-- p.home_library_code as "HOME LIBRARY",
(
	SELECT
	l.name

	FROM
	sierra_view.location_myuser as l

	WHERE
	l.code = p.home_library_code
) AS "HOME LIBRARY",
p.birth_date_gmt::date AS "BIRTH DATE",
-- EXTRACT(YEAR FROM AGE(p.birth_date_gmt)) as "AGE",
p.checkout_total AS "TOT CHKOUT",
expiration_date_gmt::date AS "EXP DATE",
-- f.prefix AS "PATRN PREFIX NAME",
f.first_name AS "PATRN FIRST NAME",
f.middle_name AS "PATRN MIDDLE NAME",
f.last_name AS "PATRN LAST NAME",
-- f.suffix AS "PATRN SUFFIX NAME",
-- get the first listed address
(
	SELECT
	addr1

	FROM
	sierra_view.patron_record_address

	WHERE
	patron_record_id = p.record_id
	
	ORDER BY
	display_order

	LIMIT 1
) AS "ADDR1",

(
	SELECT
	city || ', ' || region || ' ' || postal_code

	FROM
	sierra_view.patron_record_address

	WHERE
	patron_record_id = p.record_id
	
	ORDER BY
	display_order

	LIMIT 1
) AS "CITY STATE ZIP",
(
	SELECT
	phone_number
	FROM
	sierra_view.patron_record_phone
	WHERE
	patron_record_id = p.record_id
	ORDER BY
	display_order
	LIMIT 1	
) AS "TELEPHONE",
(	
	SELECT
	field_content
	FROM
	sierra_view.varfield
	WHERE
	record_id = p.record_id
	AND varfield_type_code = 'z'
	ORDER BY
	occ_num
	LIMIT 1
) AS "EMAIL ADDR"

FROM
sierra_view.patron_record as p

JOIN
sierra_view.patron_record_fullname as f
ON
  f.patron_record_id = p.record_id

JOIN
sierra_view.record_metadata as r
ON
  r.id = p.record_id

WHERE
-- pcode3 is "Foundation? (P3)"
p.pcode3 = 2
AND expiration_date_gmt::date >= NOW()::date
-- patron is 18 or older
AND EXTRACT(YEAR FROM AGE(p.birth_date_gmt))::INTEGER > 17::INTEGER 
;

Find patrons with more than 1 name listed

SELECT
r.record_type_code || r.record_num || 'a' as record_number,
(
	SELECT
	string_agg(v.field_content, ',' order by v.occ_num)

	FROM
	sierra_view.varfield as v

	WHERE
	v.record_id = r.id
	AND v.varfield_type_code = 'b'
) as barcodes,

(
	SELECT
	string_agg(n.last_name || ' ' || n.first_name || ' ' || n.middle_name, ', ' order by n.display_order)

	FROM
	sierra_view.patron_record_fullname as n

	WHERE
	n.patron_record_id = r.id
) as fullnames_listed

FROM
sierra_view.record_metadata as r

JOIN
sierra_view.patron_record_fullname as f
ON
  f.patron_record_id = r.id

WHERE
r.record_type_code || r.campus_code = 'p'
and r.deletion_date_gmt is null

GROUP BY
r.id, r.record_type_code, r.record_num

HAVING
count(*) > 1

Find duplicate patrons

---

Find patron records that have duplicate barcodes

If barcode number is repeated on multiple records, this will display the patron record information

sample output:

created_date;barcode;patron_record_num;patron;ptype;last_circ_activity;expiration_date
2012-07-19 02:17:11-04;49143662;p1234567a;Patron, John Q 1947-02-26;0;2015-12-22 08:38:26-05;2020-12-22 04:00:00-05
2016-06-13 14:44:03-04;49143662;p1234568a;Patron, John Q 1947-02-26;0;2016-06-13 14:44:03-04;2021-06-13 04:00:00-04
2012-07-18 22:59:21-04;50948033;p1234569a;Patron, Patty L 1986-12-12;6;2015-05-26 12:30:29-04;2013-03-06 04:00:00-05
2013-06-15 16:07:41-04;50948033;p1234570a;Patty, Patty L 1986-12-12;0;2013-07-20 11:16:24.26-04;2018-06-15 04:00:00-04

SELECT
r.creation_date_gmt as created_date,
e.index_entry as barcode,
'p' || r.record_num || 'a' as patron_record_num,
n.last_name || ', ' ||n.first_name || COALESCE(' ' || NULLIF(n.middle_name, ''), '') || ' ' || p.birth_date_gmt as patron,
p.ptype_code as ptype,
p.activity_gmt as last_circ_activity,
p.expiration_date_gmt as expiration_date

FROM
sierra_view.phrase_entry as e

JOIN
sierra_view.patron_record as p
ON
  p.record_id = e.record_id

JOIN
sierra_view.record_metadata as r
ON
  r.id = p.record_id

JOIN
sierra_view.patron_record_fullname AS n
ON
  n.patron_record_id = r.id

WHERE 
-- combining the index tag with the index entry returns more quickly
e.index_tag || e.index_entry IN (
-- 'b' || '63160170',
-- 'b' || '63274153'
	SELECT
	'b' || e.index_entry as barcode

	FROM
	sierra_view.phrase_entry AS e

	JOIN
	sierra_view.patron_record as p
	ON
	  p.record_id = e.record_id

	WHERE
	e.index_tag || e.varfield_type_code = 'bb'

	GROUP BY
	barcode

	HAVING 
	count(*) > 1
)

ORDER BY
barcode,
patron ASC

Find patrons with multiple barcodes

SELECT
'p' || r.record_num || 'a' as record_num

FROM
sierra_view.record_metadata as r

WHERE
r.id IN
(
	SELECT
	-- count(*),
	e.record_id

	FROM
	sierra_view.patron_record as p

	JOIN
	sierra_view.phrase_entry as e
	ON
	  (e.record_id = p.record_id) AND (e.index_tag = 'b') AND (e.varfield_type_code = 'b')

	GROUP BY
	e.record_id

	HAVING
	count(*) > 1

	-- ORDER BY
	-- count(*)
)

Find duplicate patrons by name + birthdate + patron code

sample output

2012-07-18 21:30:10-04;12345678;p1234567a;Patron, John Q 1977-01-01;0;2016-08-18 16:11:55-04;2019-05-10 04:00:00-04;c;$145.78
2015-01-07 13:41:06-05;12345679;p7654321a;Patron, John Q 1977-01-01;0;2016-08-21 15:04:40-04;2020-04-10 04:00:00-04;c;$214.53

SELECT
r.creation_date_gmt as created,
e.index_entry as barcode,
'p' || r.record_num || 'a' as patron_record_num,
pn.last_name || ', ' ||pn.first_name || COALESCE(' ' || NULLIF(pn.middle_name, ''), '') || ' ' || pr.birth_date_gmt as patron,
pr.ptype_code,
pr.activity_gmt,
pr.expiration_date_gmt,
pr.mblock_code as block_code,
pr.owed_amt::float8::numeric::money as owed_amt
-- pr.home_library_code,

FROM
sierra_view.patron_record_fullname as pn

JOIN
sierra_view.patron_record as pr
ON
  pr.record_id = pn.patron_record_id

JOIN
sierra_view.record_metadata as r
ON
  r.id = pr.record_id

JOIN
sierra_view.phrase_entry AS e
ON
  (e.record_id = r.id) AND (e.index_tag = 'b') AND (e.varfield_type_code = 'b')
  
WHERE
pr.birth_date_gmt || pn.first_name || COALESCE(' ' || NULLIF(pn.middle_name, ''), '') || ' ' || pn.last_name 
IN
(
	SELECT

	p.birth_date_gmt ||
	n.first_name || COALESCE(' ' || NULLIF(n.middle_name, ''), '') || ' ' || n.last_name as patron_name
	-- e.index_entry,
	-- count(*) as matches

	FROM
	sierra_view.record_metadata AS r

	JOIN
	sierra_view.patron_record AS p
	ON
	  p.record_id = r.id

	JOIN
	sierra_view.patron_record_fullname AS n
	ON
	  n.patron_record_id = r.id

	-- JOIN
	-- sierra_view.phrase_entry AS e
	-- ON
	--   (e.record_id = r.id) AND (e.index_tag = 'b') AND (e.varfield_type_code = 'b')

	WHERE 
	r.record_type_code = 'p'
	-- and r.creation_date_gmt >= '2017-05-01'

	GROUP BY
	p.birth_date_gmt,
	patron_name,
	p.ptype_code
	-- e.index_entry

	HAVING
	COUNT(*) > 1
)

ORDER BY
pn.last_name || pn.first_name || pr.birth_date_gmt || COALESCE(' ' || NULLIF(pn.middle_name, ''), ''),
pr.ptype_code ASC,
pr.activity_gmt DESC

Find duplicate patrons by birthdate + name + barcode

sample output:

"1990-01-01";"John Q Patron";"12345678";2
"1977-02-02";"Betty J Patron"; "12345679";2

SELECT

p.birth_date_gmt,
n.first_name || COALESCE(' ' || NULLIF(n.middle_name, ''), '') || ' ' || n.last_name as patron_name,
e.index_entry,
count(*) as matches

FROM
sierra_view.record_metadata AS r

JOIN
sierra_view.patron_record AS p
ON
  p.record_id = r.id

JOIN
sierra_view.patron_record_fullname AS n
ON
  n.patron_record_id = r.id

JOIN
sierra_view.phrase_entry AS e
ON
  (e.record_id = r.id) AND (e.index_tag = 'b') AND (e.varfield_type_code = 'b')

WHERE 
r.record_type_code = 'p'
-- and r.creation_date_gmt >= '2017-05-01'

GROUP BY
p.birth_date_gmt,
patron_name,
e.index_entry

HAVING
COUNT(*) > 1

---

Find duplicate patron barcodes that start with “SR”

temporary patron cards are issued barcodes starting with SR, but are sometimes accidentally duplicated. This query will find those patron record nums.

sample output:

"p1234567a";"sr123456";"2017-04-23 11:20:59-04"
"p1234568a";"sr123456";"2017-04-04 15:11:56-04"

SELECT
-- patron record number, barcode, and create date. 
'p' || r.record_num || 'a',
p.index_entry,
r.creation_date_gmt

FROM
sierra_view.phrase_entry as p

JOIN
sierra_view.record_metadata as r
ON
  r.id = p.record_id

JOIN
sierra_view.patron_record as pr
ON
  pr.record_id = r.id

where 
p.index_tag || p.index_entry IN
(
SELECT
'b' || e.index_entry

FROM
sierra_view.phrase_entry as e

JOIN
sierra_view.record_metadata as r
ON
  r.id = e.record_id

WHERE
e.index_tag || e.index_entry SIMILAR TO 'b' || 'sr[0-9]{1,}'

GROUP BY
e.index_entry

HAVING COUNT(e.index_entry) > 1
)

ORDER BY 
r.creation_date_gmt DESC

Find patrons with note of "ConnectED"

SELECT
*

FROM

sierra_view.record_metadata as r

JOIN
sierra_view.patron_record  as p
ON
  p.record_id = r.id

JOIN
sierra_view.varfield as v 
ON
  (v.record_id = r.id) AND (v.varfield_type_code = 'x') AND (v.field_content = 'ConnectED')

where r.record_type_code = 'p'