0

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) > @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.]

1 Answer 1

3

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) > @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.

2
  • Thanks, @EvanSteinbrenner. There will be people on the Employee table that I wish to include who don't have a record on EmployeeStatus, hence the LEFT JOIN. And there will be people on the EmployeeStatus that need to be excluded, thus the NOT IN. I'll use your suggestion to see what I get. Thanks again :)
    – Alex
    Jul 6, 2016 at 13:48
  • Also wondering if a PIVOT can come in handy here, like this: stackoverflow.com/questions/7145694/pivot-with-month But not sure what it would look like here
    – Alex
    Jul 6, 2016 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.