Find Out Count of Employees Joined in January Month

SQL Query to fetch number of employees joined over a calender year, broken down per month

SELECT   Trunc(EMP_JN_DT,'MM') Emp_Jn_Mth,
Count(*)
FROM EMP_REG
WHERE EMP_JN_DT between date '2009-01-01' AND date '2009-12-31'
GROUP BY Trunc(EMP_JN_DT,'MM')
ORDER BY 1;

If you do not have anyone join in a particular month then you'd get no row returned. To over come this you'd have to outerjoin the above to a list of months in the required year.

find the employee count based on hire date In Oracle

In Scott's sample schema, there's the EMP table:

SQL> select ename, hiredate
2 from emp
3 order by to_char(hiredate, 'mm');

ENAME HIREDATE
---------- --------
ADAMS 12.01.83 --> Adams and Miller
MILLER 23.01.82 --> were hired in January
ALLEN 20.02.81
WARD 22.02.81
--> nobody was hired in March
JONES 02.04.81 --> Jones was hired in April
BLAKE 01.05.81
CLARK 09.06.81
TURNER 08.09.81
MARTIN 28.09.81
KING 17.11.81
SCOTT 09.12.82
SMITH 17.12.80
JAMES 03.12.81
FORD 03.12.81

14 rows selected.

In order to get result you want, you need a calendar - separate table which contains all months in a year because table with employees doesn't contain all months (see above - nobody was hired in e.g. March).

Then you'd outer join that calendar with the original table, count number of employees and - that's it:

SQL> with calendar as
2 (select lpad(level, 2, '0') mon
3 from dual
4 connect by level <= 12
5 )
6 select to_char(to_date(c.mon, 'mm'), 'Mon', 'nls_date_language = english') hiredate_month,
7 count(e.empno) cnt
8 from calendar c left join emp e on to_char(e.hiredate, 'mm') = c.mon
9 group by c.mon
10 order by c.mon;

HIREDATE_MON CNT
------------ ----------
Jan 2
Feb 2
Mar 0
Apr 1
May 1
Jun 1
Jul 0
Aug 0
Sep 2
Oct 0
Nov 1
Dec 4

12 rows selected.

SQL>

SQL Query employees who joined in x year y month not giving desired result

While converting to a varchar appears a straightforward solution, its performance will not be optimal, because its not sargable - indexes can't be used once you start transforming columns i.e. by converting them, when used in a where clause.

Using a window compare removes the need for a function and performs better as follows:

-- Find all employees hired in 2020
SELECT *
FROM dbo.Employees
WHERE Hiredate >= '01 Jan 2020' and HireDate < '01 Jan 2021'

-- Find all employees hired in May 2020
SELECT *
FROM dbo.Employees
WHERE Hiredate >= '01 May 2020' and HireDate < '01 Jun 2020'

Retrieving active employees by month in Postgres

Use generate_series():

SELECT gs.dte, count(e.hire_date) 
FROM generate_series('2018-01-01'::date, '2018-12-01'::date, interval '1 month') gs(dte) LEFT JOIN
employees e
ON e.hire_date <= gs.dte AND
(e.deactivate_date IS NULL OR e.deactivate_date > gs.dte)
GROUP BY gs.dte
ORDER BY gs.dte;

What is the best way to get active employee count per month?

One option is to use an ad-hoc tally table. A tally/calendar table would do the trick as well

I opted for the DatePart DAY to capture any portion of the month

Example

Declare @Date1 date = '2016-01-01'
Declare @Date2 date = '2017-01-31'

Select Year = DatePart(YEAR,D)
,Month = DatePart(MONTH,D)
,EmpCnt = count(DISTINCT [EmployeeID])
From (Select Top (DateDiff(DAY,@Date1,@Date2)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@Date1) From master..spt_values n1,master..spt_values n2) A
Left Join @Employees B on D between [HireDate] and IsNull([TerminationDate],GetDate())
Group By DatePart(YEAR,D), DatePart(MONTH,D)
Order By 1,2

Returns

Year    Month   EmpCnt
2016 1 1
2016 2 1
2016 3 2
2016 4 2
2016 5 2
2016 6 1
2016 7 1
2016 8 1
2016 9 1
2016 10 1
2016 11 1
2016 12 1
2017 1 1

As Requested - Some Commentary

First we create a series of dates between X and Y. This is done via an ad-hoc tally table, Row_Number(), and DateAdd(). For example:

Declare @Date1 date = '2016-01-01'
Declare @Date2 date = '2017-01-31'

Select Top (DateDiff(DAY,@Date1,@Date2)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@Date1)
From master..spt_values n1,master..spt_values n2

Returns

D
2016-01-01
2016-01-02
2016-01-03
2016-01-04
...
2017-01-29
2017-01-30
2017-01-31

Notice that we are performing a cross join on spt_values (n1 and n2). This is because spt_values has only 2,523 records (or days). Considering that would equate to only 6 years, by using a cross join which expands the potential time span of 6.3 million days --- a ridiculous number, but you would never see that volume because we specify TOP ( nDays )

Once we have this dataset of target days, we then perform a LEFT JOIN to the EMPLOYEE table where D is between Hire and Term dates. This actually create a large temporal dataset. For example if an employee was active for only 10 days, we would see 10 records. 1 for for each day.

Then we perform a simple aggregation COUNT(DISTINCT EmployeeID) group by year and month.

Query to find number of employees by month

create table employees (id int, date_started date, date_terminated date);

insert into employees values
(1,'2012-01-01','2012-07-01'),
(2,'2012-11-01',NULL),
(3,'2010-01-01','2012-02-10');

with

time_space as (
select
gs::date as bom,
(gs + interval '1 month' - interval '1 day')::date as eom
from
generate_series('2012-01-01'::date,'2013-01-01'::date, '1 month') gs

)

select
ts.bom,
coalesce(x.employees,0) as employees
from
time_space ts
left join (
select
bom,
count(*) as employees
from
time_space ts
join employees e on (coalesce(e.date_terminated,'3000-01-01'::date) >= ts.bom and e.date_started <= ts.eom)
group by
bom) x using (bom)

SQLFiddle



Related Topics



Leave a reply



Submit