Group COUNT by month less than variable In SQL Server 2008 R2, I have the following two tables, Employee and EmployeeStatus:

CREATE TABLE [dbo].[Employee]
(
    [employeeID] [int] IDENTITY(9500,1) NOT NULL,
    [firstName] [nvarchar](50) NOT NULL,
    [middleName] [nvarchar](50) NULL,
    [lastName] [nvarchar](50) NOT NULL,
    [suffix] [nvarchar](50) NULL,
    [createdDate] [datetime] NOT NULL CONSTRAINT [DF_Employee_createdDate]  DEFAULT (getdate()),

    CONSTRAINT [PK_Employee] 
        PRIMARY KEY CLUSTERED ([employeeID] ASC)
             WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                   ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[EmployeeStatus]
(
    [employeeStatusID] [int] IDENTITY(1,1) NOT NULL,
    [employeeID] [int] NOT NULL,
    [validEmployeeStatusID] [int] NOT NULL,
    [statusChangeBeginDate] [datetime2](7) NOT NULL 
         CONSTRAINT [DF_EmployeeStatus_statusChangeBeginDate]  DEFAULT (getdate()),
    [statusChangeEndDate] [datetime2](7) NULL,
    [createdDate] [datetime2](7) NOT NULL 
         CONSTRAINT [DF_EmployeeStatus_createdDate]  DEFAULT (getdate()),

    CONSTRAINT [PK_EmployeeStatus] 
         PRIMARY KEY CLUSTERED ([employeeStatusID] ASC)
            WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


I'd like to get COUNT(distinct e.employeeID) for each month less than the @month variable. Here's what I've tried:

declare @year int = 2016, @month int = 7

select 
    month(es.statusChangeEndDate) as Month, 
    count(distinct e.employeeID) as EligibleEmployees 
from 
    Employee e
left join 
    EmployeeStatus es on e.employeeID = es.employeeID
where   
    ((es.validEmployeeStatusID is null) or 
        not exists (select 1 
                    from Employee emp 
                    join EmployeeStatus emps on emp.employeeID = emps.employeeID
                    where
                        (emps.statusChangeEndDate is null or
                        (year(emps.statusChangeEndDate) = @year
                            and month(emps.statusChangeEndDate) &gt; @month))
                ))
    group by month(es.statusChangeEndDate)


The employeeID column serves as foreign key on the EmployeeStatus table. An employee can have 0 or more statuses on the EmployeeStatus table. Note that I'm excluding employees who have a null value for statusChangeEndDate or the  month of their statusChangeEndDate is greater than @month variable (in this case 7). 

What I'd like to get is a result set like this:

Month  EligibleEmployees
=====  ===================
1      2567
2      2763
3      2595
4      2673
5      2792
6      2499


However, it returns something like this:

Month  EligibleEmployees
==========================
NULL   2567


Your helps is appreciated. Thanks. [I had this question on the main Stack Overflow but realized this is a better place for it. Deleted that question.]
Your not exists sub-query doesn't work. It will always return a 1 if ANY employee exists that has a null statusChangeEndDate or that meets the criteria you are testing so basically will always exist. To continue using a Not Exists you'd need to make it correlated sub-query by adding e.employeeID = emp.employeeID so it is checking to see if that employee doesn't have a null record or a record for later than this year. Doing it that way probably wouldn't scale well for performance as the correlated sub-query needs to be run for every line in the results.

You could change it to 

e.employeeid not in ( select emp.employeeid from Employee emp
join EmployeeStatus emps on emp.employeeID = emps.employeeID
        where
        (emps.statusChangeEndDate is null or
             (year(emps.statusChangeEndDate) = @year
               and month(emps.statusChangeEndDate) &gt; @month))


Your attempt at SQL seems to have more criteria than your description as it is including people that have nothing in the EmployeeStatus table (left join and is null) and is trying to exclude people who have a change end date after the entered date or have a null change end date. That is subtly different than finding people who have a change end date before your entered date since people can have multiple changes.