I would like to know how many staff members I have in my database for the past years. All staff members are in one table (even the ones that already left) and this table also contains their employment begin- and enddate.
I would like to have a table like this
Date ------- Members
01/01/2020 ---- 100
02/01/2020 ---- 99
31/12/2020 ---- 101
I thought I could use the following SQL query where I create a list of all previous 365 dates and calculate the number of employees:
SELECT TRUNC (SYSDATE - ROWNUM) dt,
(SELECT COUNT(DISTINCT TLC) FROM CREW_MEMBER
WHERE crew_member.employment_begin_dt <= TRUNC (SYSDATE - ROWNUM)
AND
CREW_MEMBER.EMPLOYMENT_END_DT >= TRUNC(SYSDATE - ROWNUM)
) AANTAL_CREW
FROM DUAL
CONNECT BY ROWNUM < 366
This is however not giving me the result I want as I am getting the same values for all dates. I think this is because the inner select statement is using it's own rows instead of the ones from my DUAL table.
Is there something I can do to accomplish this?
Thank you!
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