rayvoelker

Finding Original Circulation

finding checkouts without any paired "filled hold" transactions will be how we define an "original circ".

the idea is that we match a "filled hold" with a 'checkout' based on 4 points of data op_code, patron_record_id, item_record_id, transaction_gmt

rayvoelker

{
  "config": {"view": {"continuousWidth": 400, "continuousHeight": 300}},
  "layer": [
    {
      "mark": {"type": "bar", "color": "#0090bd"},
      "encoding": {
        "tooltip": [
          {"type": "nominal", "field": "median_income_range"},
          {"type": "quantitative", "field": "percent_over_10usd"},
          {"type": "quantitative", "field": "total_patrons"},

rayvoelker

{
  "config": {"view": {"continuousWidth": 400, "continuousHeight": 300}},
  "vconcat": [
    {
      "data": {"name": "data-465faa1de7ff9354eb5c0b9c2a57536b"},
      "mark": "line",
      "encoding": {
        "color": {"type": "quantitative", "field": "overdue_count"},
        "x": {"type": "ordinal", "field": "iso8601_week"},
        "y": {

rayvoelker

-- EXPLAIN QUERY PLAN

with ops as (

	with variables as (
		SELECT
		'2020-07-19' as min_date,
		'2020-07-26' as max_date,
		'1' as location_code
	)

rayvoelker

{
  "config": {"view": {"continuousWidth": 400, "continuousHeight": 300}},
  "vconcat": [
    {
      "data": {"name": "data-c73cbde8aa602c2cd52c8984bda1552f"},
      "mark": "bar",
      "encoding": {
        "color": {"type": "nominal", "field": "circ_op"},
        "tooltip": [
          {"type": "ordinal", "field": "date"},

rayvoelker

-- first import the weekly circ_trans data into an sqlite database
-- next adjust the date range
-- export the data into another .csv

-- NOTE: date range is calendar week ... NOT ISO WEEK

with id_range as (
	select
	min(id) as min_id,
	max(id) as max_id

rayvoelker

-- find all the "fines purged" in the fines_paid table by specific date.
-- group them by 

DROP TABLE IF EXISTS temp_patron_purge_amts;

CREATE TEMP TABLE temp_patron_purge_amts AS 

SELECT
date(p.paid_date_gmt) as date_purged,
case cast( charge_type_code as text)

rayvoelker

-- find all the "fines purged" in the fines_paid table by specific date.
-- group them by the charge type

DROP TABLE IF EXISTS temp_patron_purge_amts;

CREATE TEMP TABLE temp_patron_purge_amts AS 

SELECT
date(p.paid_date_gmt) as date_purged,
case cast( charge_type_code as text)

rayvoelker

-- explain query plan
with backdate_trans as 
(
	select
	c.id,
	c.transaction_gmt as backdate_transaction_gmt,
	(
		with min_id as (
			select
			max(id) as id

rayvoelker

-- this will find backdated checkin transactions from the circ_trans table, and
-- will attempt to place a date that transaction with an ACTUAL date
DROP TABLE IF EXISTS temp_stat_groups;
CREATE TEMP TABLE temp_stat_groups AS
SELECT
s.code AS stat_group_code_num,
s.location_code AS location_code,
s.name as name

FROM