-- Copyright (c) 2022. Harvard University
--
-- Developed by Research Software Engineering,
-- Faculty of Arts and Sciences, Research Computing (FAS RC)
-- Author: Michael A Bouzinier
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
CREATE SCHEMA IF NOT EXISTS cms;
CREATE OR REPLACE PROCEDURE medicaid.create_eligibility_by_beneficiary()
LANGUAGE plpgsql
AS $$
DECLARE
cur_bene_id VARCHAR;
bene_cursor CURSOR FOR
SELECT bene_id
FROM medicaid.beneficiaries AS b
WHERE NOT EXISTS (
SELECT * FROM medicaid.e2 AS e
WHERE b.bene_id = e.bene_id
)
;
BEGIN
FOR bene_rec in bene_cursor LOOP
cur_bene_id := bene_rec.bene_id;
INSERT INTO medicaid.e2
SELECT * FROM medicaid._eligibility AS _e
WHERE _e.bene_id = cur_bene_id
;
COMMIT;
RAISE NOTICE 'bene_id = %', cur_bene_id;
END LOOP;
END;
$$;
CREATE OR REPLACE PROCEDURE medicaid.create_eligibility_by_year_state()
LANGUAGE plpgsql
AS $$
DECLARE
s VARCHAR;
y INT;
ts TIMESTAMP;
y_cursor CURSOR FOR
SELECT generate_series as y FROM generate_series(1999,2030);
state_cursor CURSOR FOR
SELECT DISTINCT state_id FROM public.us_states ORDER BY 1;
BEGIN
DROP TABLE IF EXISTS medicaid.e2 CASCADE;
CREATE TABLE medicaid.e2 AS SELECT * FROM medicaid._eligibility WITH NO DATA;
ALTER TABLE medicaid.eligibility ADD PRIMARY KEY (bene_id, year, state, month);
DROP TABLE IF EXISTS medicaid_audit.ecr CASCADE;
CREATE TABLE medicaid_audit.ecr (
state VARCHAR(2),
year INT,
ts TIMESTAMP,
duration INTERVAL
);
FOR s_rec in state_cursor LOOP
s := s_rec.state_id;
FOR y_rec in y_cursor LOOP
SELECT now() into ts;
y := y_rec.y;
INSERT INTO medicaid.e2
SELECT * FROM medicaid._eligibility AS _e
WHERE _e.state = s AND _e.year = y
;
COMMIT;
--RAISE NOTICE '%:%', s, y;
INSERT INTO medicaid_audit.ecr (
state, year, ts, duration
) VALUES (
s, y, now(), now() - ts
);
END LOOP;
END LOOP;
END;
$$;