sql - Total Count of Active Employees by Date -

i have in past written queries give me counts date (hires, terminations, etc...) follows:

select per.date_start "date",   count(peo.employee_number) "hires"  hr.per_all_people_f peo,   hr.per_periods_of_service per  per.date_start between peo.effective_start_date , peo.effective_end_date  , per.date_start between :perstart , :perend  , per.person_id = peo.person_id  group per.date_start 

i looking create count of active employees date, not sure how date query use range determine active such:

select count(peo.employee_number) "ct"  hr.per_all_people_f peo  peo.current_employee_flag = 'y'  , trunc(sysdate) between peo.effective_start_date , peo.effective_end_date 

here simple way started. works effective , end dates in data:

select thedate,        sum(num) on (order thedate) numactives ((select effective_start_date thedate, 1 num hr.per_periods_of_service) union       (select effective_end_date thedate, -1 num hr.per_periods_of_service)      ) dates 

it works adding 1 person each start , subtracting 1 each end (via num) , doing cumulative sum. might have duplicates dates, might aggregation eliminate duplicates:

select thedate, max(numactives) (select thedate,              sum(num) on (order thedate) numactives       ((select effective_start_date thedate, 1 num hr.per_periods_of_service) union             (select effective_end_date thedate, -1 num hr.per_periods_of_service)            ) dates      ) t group thedate; 

if want dates, best start calendar table, , use simple variation on original query:

select c.thedate, count(*) numactives calendar c left outer join      hr.per_periods_of_service pos      on c.thedate between pos.effective_start_date , pos.effective_end_date group c.thedate;