Jack A Ross jackross

## jsonb_object_to_property_bag.pgsql
--# :down

DROP FUNCTION IF EXISTS jsonb_object_to_property_bag(jsonb, text, text)
;

--#

--# :up

CREATE FUNCTION jsonb_object_to_property_bag(jsonb, text = 'key', text = 'value')

## changes_by_version.sql
WITH _data AS
(
  SELECT
     *
    ,EXTRACT(YEAR FROM invoice_date + INTERVAL '3 MONTHS') AS season
  FROM public.dcp_sales_log
)
,_new AS
(
  SELECT

## config.yaml
name: Example Strategy

strategy: base_acre

geography: &geography
  include:
    states:
      AR: all_counties
      LA: all_counties
      MS: all_counties

## extract_unique_names.sh
sort -uf \
  <(cut -d'|' -f2 growers_20150608.txt) \
  <(cut -d'|' -f3 growers_20150608.txt) \
  >! names.txt

## reset.sql
USE usga_apps;
GO

TRUNCATE TABLE seer.forecasts;
GO

INSERT INTO seer.forecasts
SELECT *
FROM USGAPROD2.usga_apps.seer.forecasts f
WHERE EXISTS

## compare_mail_plan.sql
DECLARE @forecast_snapshot_id uniqueidentifier = '54266D12-8B13-4C23-9BDF-A740580ECFB2';

WITH _sources AS
(
  SELECT
     CASE
       WHEN source LIKE 'MB[QL]%'
       THEN LEFT(source, 7)
       ELSE LEFT(source, 9)
     END                               AS label

## gist:54a9ab5e94ec2a7c69ed
begin;

/*
Problem: I don't want the application to really care about how the data is
structured in the database. The application / view code needs to work
with the data in a sane format, without worrying about normalization, joins,
eager fetching, etc.

DB Schema Overview:
  orders

## december_2010_2014_payment_month.txt

----------------------------------------------------------------
Sourcing data from facts.memberships for Dec 2010
Sourcing data from facts.memberships for Dec 2009

Payment Month  # Pmts  Payment $  Avg Pmt $  Reg Pmt $  Def Pmt $  % Def $
------------- ------- ---------- ---------- ---------- ---------- --------
Dec 2009        77993    2229775      28.59    2162510      67255  0.03016
Dec 2010        81479    2284552      28.04    2206707      77845  0.03407

## november_2011_2015_payment_month.txt

----------------------------------------------------------------
Sourcing data from facts.memberships for Nov 2011
Sourcing data from facts.memberships for Nov 2010

Payment Month  # Pmts  Payment $  Avg Pmt $  Reg Pmt $  Def Pmt $  % Def $
------------- ------- ---------- ---------- ---------- ---------- --------
Nov 2010        32171    1186334      36.88     290652     895682  0.75500
Nov 2011        28318    1054941      37.25     231613     823328  0.78045

## october_2011_2015_payment_month.txt

----------------------------------------------------------------
Sourcing data from facts.memberships for Oct 2011
Sourcing data from facts.memberships for Oct 2010

Payment Month  # Pmts  Payment $  Avg Pmt $  Reg Pmt $  Def Pmt $  % Def $
------------- ------- ---------- ---------- ---------- ---------- --------
Oct 2010        34017    1313515      38.61     425800     887715  0.67583
Oct 2011        35298    1278274      36.21     441042     837232  0.65497
	--# :down

	DROP FUNCTION IF EXISTS jsonb_object_to_property_bag(jsonb, text, text)
	;

	--#

	--# :up

	CREATE FUNCTION jsonb_object_to_property_bag(jsonb, text = 'key', text = 'value')
	WITH _data AS
	(
	SELECT
	*
	,EXTRACT(YEAR FROM invoice_date + INTERVAL '3 MONTHS') AS season
	FROM public.dcp_sales_log
	)
	,_new AS
	(
	SELECT
	name: Example Strategy

	strategy: base_acre

	geography: &geography
	include:
	states:
	AR: all_counties
	LA: all_counties
	MS: all_counties
	sort -uf \
	<(cut -d'\|' -f2 growers_20150608.txt) \
	<(cut -d'\|' -f3 growers_20150608.txt) \
	>! names.txt
	USE usga_apps;
	GO

	TRUNCATE TABLE seer.forecasts;
	GO

	INSERT INTO seer.forecasts
	SELECT *
	FROM USGAPROD2.usga_apps.seer.forecasts f
	WHERE EXISTS
	DECLARE @forecast_snapshot_id uniqueidentifier = '54266D12-8B13-4C23-9BDF-A740580ECFB2';

	WITH _sources AS
	(
	SELECT
	CASE
	WHEN source LIKE 'MB[QL]%'
	THEN LEFT(source, 7)
	ELSE LEFT(source, 9)
	END AS label
	begin;

	/*
	Problem: I don't want the application to really care about how the data is
	structured in the database. The application / view code needs to work
	with the data in a sane format, without worrying about normalization, joins,
	eager fetching, etc.

	DB Schema Overview:
	orders

	----------------------------------------------------------------
	Sourcing data from facts.memberships for Dec 2010
	Sourcing data from facts.memberships for Dec 2009

	Payment Month # Pmts Payment $ Avg Pmt $ Reg Pmt $ Def Pmt $ % Def $
	------------- ------- ---------- ---------- ---------- ---------- --------
	Dec 2009 77993 2229775 28.59 2162510 67255 0.03016
	Dec 2010 81479 2284552 28.04 2206707 77845 0.03407