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

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.