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

Question

I have Employee like below:

DECLARE @Employees TABLE
(
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[HireDate] [datetime] NOT NULL,
[TerminationDate] [datetime] NULL
)

INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('2016/01/01','2016/01/02')
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('2016/02/01', '2017/01/30')
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('2016/03/01', '2016/05/05')

If I need to know the count of active employees for Feb 2016, I used below query:

SELECT * FROM @Employees
WHERE HireDate <= '2016-02-28' AND TerminationDate >= '2016-02-28'

However, I'm having difficulty on an easy method to find active employees for each month. For example, I want to know count of active employees from Jan 2016 to Jan 2017 every month.

Do I need to have separate table with each month and use some CTE to cross reference both tables and provide report for every month? Any directions will be grateful.

With the inputs so far, I have got to this. It seems to be working fine except for Jan 2016 where I have one employee active though only for 2 days, it is not reporting since I know I'm validating month-end. Any tweaks?

DECLARE @startDate DATETIME
DECLARE @endDate datetime
SET @startDate='2014-01-31'
SET @endDate='2017-05-31'

DECLARE @Employees TABLE
(
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [HireDate] [datetime] NOT NULL,
    [TerminationDate] [datetime] NULL
)

INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('2016/01/01','2016/01/02')
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('2016/02/01', '2017/01/30')
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('2016/03/01', '2016/05/05')

;With MyListOfDates( MyCalendarMonthEnd ) 
AS
(
    SELECT @startDate MyCalendarMonthEnd

    UNION ALL

    SELECT DATEADD(MONTH, 1, MyCalendarMonthEnd)
    FROM MyListOfDates
    WHERE MyCalendarMonthEnd < @endDate
)
SELECT YEAR(mld.MyCalendarMonthEnd) Year, MONTH(mld.MyCalendarMonthEnd)  Month, COUNT(*) ActiveEmployeeCount
FROM MyListOfDates mld
JOIN @Employees e  on 1 = 1
WHERE e.HireDate <= mld.MyCalendarMonthEnd and e.TerminationDate >= mld.MyCalendarMonthEnd
GROUP BY mld.MyCalendarMonthEnd

Answer 1

Hope this query helps. Instead of using a CTE, this could be another way to get the count:

SELECT count(EmployeeID) as Count_Emp, format(hiredate, 'MMM', 'en-US') FROM Employees
WHERE TerminationDate IS NULL /* All the active employees won't have a TerminationDate */
group by EmployeeId, hiredate

Answer 2

I have already queried @Techspider to explain the output in tabular form.

I am not using ROW_Number or distinct.

I am not using CROSS Join because My output is Each Month,Each Year (not each day,each month,each year).

Also you have to find each month count

Also finding count for such long duration will slow down

Try this,

DECLARE @startDate DATETIME
DECLARE @endDate datetime
SET @startDate='2016-01-01'
SET @endDate='2017-01-31'

DECLARE @Employees TABLE
(
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [HireDate] [datetime] NOT NULL,
    [TerminationDate] [datetime] NULL
)

INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('2016/01/01','2016/01/02')
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('2016/02/01', '2017/01/30')
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('2016/03/01', '2016/05/05')

 SELECT datepart(year,EDT)[Year],datepart(month,edt)[Month]
 ,count( e.[EmployeeID]) EmpCount
  FROM 
 (SELECT  dateadd(month,number,@startDate)STDT
 ,dateadd(day,-1,dateadd(month,datediff(month,0,(dateadd(month,number,@startDate)))+1,0)) EDT
  FROM MASTER.dbo.spt_values
     WHERE name is null and number<=datediff(month,@startDate,@endDate)+1)n
     left join @Employees E on
  HireDate <= n.STDT
 AND TerminationDate >=  n.EDT
 group by datepart(year,EDT),datepart(month,edt)
 order by 1,2

Answer 3

In case anyone interested in the solution using CTEs. Preferred solution is provided by @JohnCappelleti

DECLARE @startDate DATETIME
DECLARE @endDate datetime
SET @startDate='2014-01-31'
SET @endDate='2017-05-31'

DECLARE @Employees TABLE
(
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [HireDate] [datetime] NOT NULL,
    [TerminationDate] [datetime] NULL
)

INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('2016/01/01','2016/01/02')
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('2016/02/01', '2017/01/30')
INSERT INTO @Employees (HireDate, TerminationDate) VALUES ('2016/03/01', '2016/05/05')

;With MyListOfDates( MyCalendarMonthEnd ) 
AS
(
    SELECT @startDate MyCalendarMonthEnd

    UNION ALL

    SELECT DATEADD(DAY, 1, MyCalendarMonthEnd)
    FROM MyListOfDates
    WHERE MyCalendarMonthEnd < @endDate
)
SELECT YEAR(mld.MyCalendarMonthEnd) Year, MONTH(mld.MyCalendarMonthEnd)  Month, COUNT(DISTINCT EmployeeID) ActiveEmployeeCount
FROM MyListOfDates mld 
JOIN @Employees e  on 1 = 1
WHERE e.HireDate <= mld.MyCalendarMonthEnd and e.TerminationDate >= mld.MyCalendarMonthEnd
GROUP BY YEAR(mld.MyCalendarMonthEnd), MONTH(mld.MyCalendarMonthEnd)
ORDER BY 1,2
OPTION (MAXRECURSION 0)

Answer 4

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.