0

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;
$$;
Amir Charkhi
  • 603
  • 6
  • 19
Ken
  • 47
  • 5

0 Answers0