I want to be able to pass 2 variables to my DO LOOP (effectivedate, and nbr_mem_months. These variables are currently hardcoded into the DO LOOP in the Declare statement. This logic counts the number of months that a member is effective with the health plan and adds an increment to the corresponding year and month bucket. So for each row that the query from elan.elig, the DO LOOP will add increments to the YearMonth Bucket. So if the member has an effective date of 201910131 and a term date of 20190531, the DO LOOP will add 1 to each of the buckets:
201901
201902
201903
201904
201905
select (extract(year from age(case when terminationdate is null then
CURRENT_DATE else terminationdate END ,effectivedate ))) *12 +
(extract(month from age(case when terminationdate is null then
CURRENT_DATE else terminationdate END ,effectivedate )) +1)
as "effectivedate" ,to_char(effectivedate,'YYYYMM') as nbr_mem_months
from elan.elig
DO $$
declare
nbr_mem_months integer=5;
effectivedate date='2019-04-01';
ym char(6) =to_char(effectivedate,'YYYYMM');
begin
for r in 1..nbr_mem_months loop
update elan.pmpm set mbrmonths=mbrmonths+1 where yyyyymm=ym;
effectivedate=effectivedate + interval '1 month';
ym=to_char(effectivedate,'YYYYMM');
end loop;
end;
$$;