OpenOUSD Annual Data Update

check_for_missing_codes.sql

SELECT 
DISTINCT(site_code) from expenditures

where year = 2021 AND 
site_code NOT IN (SELECT DISTINCT(code) from sites)




SELECT 
DISTINCT(object_code) from expenditures

where year = 2021 AND 
object_code NOT IN (SELECT DISTINCT(code) from objects)
ORDER BY object_code ASC

-- good site for looking up resources
-- https://www2.cde.ca.gov/sacsquery/querybyresource.asp

SELECT 
DISTINCT(resource_code) from expenditures

where year = 2021 AND 
resource_code NOT IN (SELECT DISTINCT(code) from resources)
ORDER BY resource_code ASC

SELECT 
DISTINCT(function_code) from expenditures

where year = 2021 AND 
function_code NOT IN (SELECT DISTINCT(code) from functions)
ORDER BY function_code ASC

-------------------------------------- Staffing Data

-- check for missing Job Classes
SELECT DISTINCT(staffing.job_class_id), staffing.year FROM staffing

WHERE TRIM(staffing.job_class_id) not in (SELECT DISTINCT( TRIM(jc.job_class_id) ) from job_classes jc)

--- check for missing bargaining units
SELECT DISTINCT(staffing.bargaining_unit_id), staffing.year FROM staffing

WHERE TRIM(staffing.bargaining_unit_id) not in (SELECT DISTINCT( TRIM(staffing.bargaining_unit_id) ) from bargaining_units jc)

sample_inserts.sql

--- Sites
insert into
  "sites" (
    "category",
    "code",
    "description",
    "type",
    "year_entered"
  )
values
  (
    'Elementary',
    160,
    'Lockwood Steam Academy',
    'Schools',
    2021
  );

insert into
  "sites" (
    "category",
    "code",
    "description",
    "type",
    "year_entered"
  )
values
  (
    'Elementary',
    169,
    'Oakland Academy of Knowledge',
    'Schools',
    2021
  );

insert into
  "sites" (
    "category",
    "code",
    "description",
    "type",
    "year_entered"
  )
values
  (
    'Other',
    997,
    'Transfers to Charter Schools',
    'Central',
    2021
  );

insert into
  "sites" (
    "category",
    "code",
    "description",
    "type",
    "year_entered"
  )
values
  (
    'Other',
    982,
    'Budget Plug at Adoption',
    'Central',
    2021
  );
  
-- Objects

insert into
  "objects" ("code", "description", "short")
values
  (
    5752,
    'Direct Interfund Project Manager',
    'Direct Interfund Proj Mgr'
  );

insert into
  "objects" ("code", "description", "short")
values
  (5873, 'Security / Alarms', 'Security/Alarms');

insert into
  "objects" ("code", "description", "short")
values
  (6160, 'Surveys', 'Surveys');

insert into
  "objects" ("code", "description", "short")
values
  (6180, 'Utility Hookup Fees', 'Utility Hookup Fees');

insert into
  "objects" ("code", "description", "short")
values
  (
    6230,
    'Improvement Of Buildings (remodeling)',
    'Improv Of Bldgs (remodeling)'
  );
  
-- Resources: there are often enough new ones to warrant importing as CSV

-- Functions
insert into "public"."functions" ("code", "description") values (1190, 'Spec Ed, Oth Spec Instruc Svcs');
insert into "public"."functions" ("code", "description") values (2150, 'Instructional Admin');
insert into "public"."functions" ("code", "description") values (8700, 'Facilities Rents & Leases');

-- using a temp table to update job class descriptions
UPDATE job_classes jc
SET    description = t.description
FROM   temp_classes t
WHERE  jc.job_class_id_whitespace = t.job_class
AND jc.year_introduced = 2021;