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
Delete Rows With Date Older Than 30 Days With SQL Server Query
How to Retrieve Records for Last 30 Minutes in Ms SQL
Return Dummy Value If No Row Present for in Clause
Sql Call Stored Procedure for Each Row Without Using a Cursor
SQL Server Dynamic PIVOT Query
Difference Between "INNER JOIN" and "OUTER JOIN"
Multiple Insert With Same Column (Ms Access)
Sql Server:How to Test If a String Has Only Digit Characters
Can You Delete Data from Influxdb
Sql - Inserting a Row and Returning Primary Key
Jsonb Array Contains Like or and and Operators
How to Select Only 1 Row from Oracle SQL
Explode and Implode Strings in MySQL Query
Daily Report by Date With Mssql for Mutiple Column
SQL Select Only Rows with Max Value on a Column
Sql Query to Show Missing Records from Another Table
Laravel Eloquent Mutiple Counts With Different Conditions in One Query