If you want a row per day, then you do need to generate all the dates in the range (unless all dates are already available in the table, but you did not tell that). For this, I like to use standard recursive CTEs.
You can then bring the table with a
left join, and aggregate:
I would recommend:
with all_dates (dt) as ( select trunc(sysdate) from dual union all select dt - 1 from all_dates where dt > trunc(sysdate) - interval '12' month ) select d.dt, count(distinct cm.employment_begin_dt) cnt from all_dates d left join crew_member cm on cm.employment_begin_dt <= d.dt and cm.employment_end_dt >= d.dt group by d.dt
CLICK HERE to find out more related problems solutions.