Looping with Dates in SQL

Question

I'm working in SQL Developer and calculating the average amount for all active cases at the end of a given month. The way I’ve written it, if I want results for every month in the past year, I have to re-run the code 12 times:

    -- DEFINE month_end = '28/02/21';
    -- DEFINE month_end = '31/03/21';
    DEFINE month_end = '30/04/21';


    with active_at_month_end as (
         SELECT amount
         FROM table
         WHERE start_date <= '&month_end'
              AND end_date > '&month_end'
         )
   
    SELECT  extract(year from to_date('&month_end','DD/MM/YY')) as year,
            extract(month from to_date('&month_end','DD/MM/YY')) as month,
            avg(amount) as avg_amount
    FROM active_at_month_end 

Is there a way I could rewrite it (maybe using a for loop?) so I only have to run it once and get results like this?

Year	Month	avg_amt
2021	2	###
2021	3	###
2021	4	###

Answer 1

If you're using Oracle, you may use something like below -

DECLARE
  month_end DATE := '31-DEC-2020'; -- assuming you want to display output from Jan-21
  no_Of_Months NUMBER := 12; -- if you want to display 12 months 
BEGIN
FOR i IN 1 .. no_Of_Months 
LOOP
month_end := ADD_MONTHS(month_end, 1); -- output will start from Jan-2021

Select year, month, avg(amount) as avg_amount
from 
(SELECT extract(year from month_end) as year,
        extract(month from month_end) as month,
        amount
        FROM table
        WHERE start_date <= month_end
          AND end_date > month_end)
) temp
group by year, month;
END LOOP;
END;