All geek questions in one place

Total active employees by date

I have in the past written requests that give me a count by date (hiring, completion, etc.) as follows:

SELECT per.date_start AS "Date", COUNT(peo.EMPLOYEE_NUMBER) AS "Hires" FROM hr.per_all_people_f peo, hr.per_periods_of_service per WHERE per.date_start BETWEEN peo.effective_start_date AND peo.EFFECTIVE_END_DATE AND per.date_start BETWEEN :PerStart AND :PerEnd AND per.person_id = peo.person_id GROUP BY per.date_start

Now I was trying to create a count of active employees by date, however, I'm not sure how I will indicate the request, since I use a range to determine the active as such:

 SELECT COUNT(peo.EMPLOYEE_NUMBER) AS "CT" FROM hr.per_all_people_f peo WHERE peo.current_employee_flag = 'Y' and TRUNC(sysdate) BETWEEN peo.effective_start_date AND peo.EFFECTIVE_END_DATE
+4
source share
4 answers

Here is an easy way to get started. This works for all effective and ending dates in your data:

 select thedate, SUM(num) over (order by thedate) as numActives from ((select effective_start_date as thedate, 1 as num from hr.per_periods_of_service) union all (select effective_end_date as thedate, -1 as num from hr.per_periods_of_service) ) dates

It works by adding one person for each run and subtracting it for each end (via num ) and making a cumulative sum. This may have duplicate dates, so you can also perform aggregation to eliminate these duplicates:

 select thedate, max(numActives) from (select thedate, SUM(num) over (order by thedate) as numActives from ((select effective_start_date as thedate, 1 as num from hr.per_periods_of_service) union all (select effective_end_date as thedate, -1 as num from hr.per_periods_of_service) ) dates ) t group by thedate;

If you really need all the dates, it's best to start with a calendar table and use a simple option in the original query:

 select c.thedate, count(*) as NumActives from calendar c left outer join hr.per_periods_of_service pos on c.thedate between pos.effective_start_date and pos.effective_end_date group by c.thedate;
+2
source

If you want to count all employees who were active during the entire range of input dates

 SELECT COUNT(peo.EMPLOYEE_NUMBER) AS "CT" FROM hr.per_all_people_f peo WHERE peo.[EFFECTIVE_START_DATE] <= :StartDate AND (peo.[EFFECTIVE_END_DATE] IS NULL OR peo.[EFFECTIVE_END_DATE] >= :EndDate)
0
source

Here is my example based on Gordon Linoff . with a slight modification, because in the SUBSTRACT table all records appeared from -1 in NUM, even if the date was not in END DATE = NULL.

 use AdventureWorksDW2012 --using in MS SSMS for choosing DATABASE to work with -- and may be not work in other platforms select t.thedate ,max(t.numActives) AS "Total Active Employees" from ( select dates.thedate ,SUM(dates.num) over (order by dates.thedate) as numActives from ( ( select StartDate as thedate ,1 as num from DimEmployee ) union all ( select EndDate as thedate ,-1 as num from DimEmployee where EndDate IS NOT NULL ) ) AS dates ) AS t group by thedate ORDER BY thedate

worked for me, hope this helps someone

0
source

I managed to get the results that I was looking for:

 --Active Team Members by Date SELECT "a_date", COUNT(peo.EMPLOYEE_NUMBER) AS "CT" FROM hr.per_all_people_f peo, (SELECT DATE '2012-04-01'-1 + LEVEL AS "a_date" FROM dual CONNECT BY LEVEL <= DATE '2012-04-30'+2 - DATE '2012-04-01'-1 ) WHERE peo.current_employee_flag = 'Y' AND "a_date" BETWEEN peo.effective_start_date AND peo.EFFECTIVE_END_DATE GROUP BY "a_date" ORDER BY "a_date"
0
source

Source: https://habr.com/ru/post/1482014/

More articles:


All Articles