CODEWITHSUNDEEP
sqloracle-databasedatetimecountrecursive-query
Sven Cozijn
Sven Cozijn

Reputation: 119

ORACLE SQL - query based on different dates

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!

Upvotes: 0

Views: 227

Answers (2)

GMB
GMB

Reputation: 222462

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269783

You can unpivot the data and keep track of "in" crew members and "out" crew members. A cumulative sum (and aggregation) then gets the total for each day:

select dte,
       sum(inc) as change_on_day,
       sum(sum(inc)) over (order by dte) as active_on_day
from ((select cm.employment_begin_dt as dte, 1 as inc
       from crew_member cm
      ) union all
      (select cm.employment_end_dt + interval '1' day as dte, -1 as inc
       from crew_member cm
       where employment_end_dt is not null
      )
     ) e
group by dte 
order by dte;

Upvotes: 0

Related Questions