DOE significant utilization

[SPTKL]

Problem:

• Schema changed,
• Secondary dbns are now just new rows, instead of another column.
• One primary building can have multiple secondary addresses. need instructions on how to handle those info
  So additional transformation is needed.
• Also we no longer have at_scale_enroll

new query:

WITH filtered as (
	SELECT *
	FROM doe_all_proposals.latest b
	WHERE pep_status = 'Approved'
)
SELECT 
	distinct ppl_id_new, 
	ppl_title as title,
	at_scale_yr as at_scale_year,
	(CASE 
		WHEN EXTRACT(month from pep_vote_date::timestamp) < 9
			THEN (EXTRACT(year from pep_vote_date::timestamp) - 1)::text||
				'-'||EXTRACT(year from pep_vote_date::timestamp)::text
		WHEN EXTRACT(month from pep_vote_date::timestamp) >= 9
		 	THEN EXTRACT(year from pep_vote_date::timestamp)::text||
			'-'||(EXTRACT(year from pep_vote_date::timestamp) + 1)::text
		ELSE NULL
	END) as school_year,
	pep_vote_date::timestamp as vote_date,
	(
		SELECT distinct a.building_code 
		FROM filtered a 
		WHERE a.ppl_id_new=b.ppl_id_new 
		AND impacted_buildings_impact_type~*'Primary'
	)as bldg_id,

	array(
		SELECT distinct a.building_code 
		FROM filtered a 
		WHERE a.ppl_id_new=b.ppl_id_new 
		AND impacted_buildings_impact_type~*'Secondary'
	)as bldg_id_additional,
	NULL as at_scale_enroll
FROM filtered b

old query:

SELECT
	main_building_id as bldg_id,
	RIGHT(dbn, 4) as org_id,
	other_impacted_building as bldg_id_additional,
	proposal_title as title,
	at_scale_year as at_scale_year,
	(CASE 
		WHEN EXTRACT(month from pep_vote::timestamp) < 9
			THEN (EXTRACT(year from pep_vote::timestamp) - 1)::text||
				'-'||EXTRACT(year from pep_vote::timestamp)::text
		WHEN EXTRACT(month from pep_vote::timestamp) >= 9
		 	THEN EXTRACT(year from pep_vote::timestamp)::text||
			'-'||(EXTRACT(year from pep_vote::timestamp) + 1)::text
		ELSE NULL
	END) as school_year,
	NULLIF(
		regexp_replace(SPLIT_PART(at_scale_school_enrollment, '-', 1), '[^0-9]|\s', '', 'g'), 
		'')::integer as at_scale_enroll,
	pep_vote as vote_date
FROM doe_all_proposals."2019/07/16"
WHERE approved = 'Approved';