Trying to loop a query to get monthly counts

I need to get the number of employees who had active coverage for each package, per month. Looking at a span of 50 months, so don’t want to run it 50 times. I need the number of people with each package on the last day of each month between these dates

I’m sure I can write a loop to check for each date, but can’t for the life of me figure it out.

SELECT COUNT (DISTINCT t.employee_num)
       ,t.PACKAGE
FROM TTABLE2 t
WHERE '&AS_OF' BETWEEN t.from_date AND t.to_date
GROUP BY 
       t.PACKAGE

AS_OF_DATE  BENEFIT Employee_NUM    FROM_DATE   TO_DATE
2015-04-30  Life    000009595   2015-04-01  2015-12-31
2015-04-30  Health  000009595   2015-04-01  2016-03-31
2015-04-30  Life    000009983   2015-04-01  2015-12-31
2015-04-30  Life    000011066   2015-04-01  2015-12-31
2015-04-30  Health  000011066   2015-04-01  2016-03-31
2015-04-30  Life    000011093   2015-04-01  2015-12-31
2015-04-30  Health  000011093   2015-04-01  2016-03-31
2015-04-30  Life    000011100   2015-04-01  2015-12-31
2015-04-30  Health  000011100   2015-04-01  2016-03-31

Answer

SELECT   d.as_of_date, 
         Benefit COUNT(DISTINCT employee_number)
FROM     ttable2 t
JOIN     (SELECT     LAST_DAY(ADD_MONTHS(fd, LEVEL)) AS as_of_date
          FROM       (SELECT MIN(from_date) AS fd, MAX(to_date) AS td
                      FROM   ttable2)
          CONNECT BY LAST_DAY(ADD_MONTHS(fd, LEVEL)) < td) d ON 
         D.as_of_date BETWEEN t.from_date AND t.to_date
GROUP BY d.as_of_date, PACKAGE

You could use the connect by syntax to generate a series of last days in each month for the appropriate range, and then join that on a count distinct employee numbers:

SELECT   as_of_date, package, COUNT(DISTINCT employee_num)
FROM     ttable2 t
JOIN     (SELECT     LAST_DAY(ADD_MONTH(fd, LEVEL)) AS as_of_date
          FROM       (SELECT MIN(from_date) AS fd, MAX(to_date) AS td
                      FROM   ttable2)
          CONNECT BY LAST_DAY(ADD_MONTH(fd, LEVEL)) < td) d ON 
         s.as_of_date BETWEEN t.from_date AND t.to_date
GROUP BY as_of_date, pacakge