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.

Leave a Comment

Your email address will not be published.

Scroll to Top