CODEWITHSUNDEEP
sqlsql-server
Json T
Json T

Reputation: 65

Get employees still employed in August

I am trying to get records of employees who are still working and those who resigned in August. Here is my query:

DECLARE @MontStart datetime,@MonthEnd datetime
set @MontStart =cast('8/1/2015' as datetime)
set @MonthEnd = cast('8/31/2015' as datetime)

Select * from ( Select EmployeeNo, (Select LastName+','+FirstName from EmpPersonalInfo where EmployeeNo=s1.EmployeeNo) as EmployeeName,(Select Classtitle from Classification where ClassCode=s1.ClassCode) as Classification,Status,s1.EffectivityDateFrom,s1.EffectivityDateTo,
        ROW_NUMBER() OVER (PARTITION BY EmployeeNo ORDER BY Status desc,cast(EffectivityDateTo as date) desc) AS Priority
        FROM Employmenthistory s1)S2 where  Priority=1 and (LTRIM(RTRIM(EmployeeNo))<>'' and NOT(EmployeeNo=''))
         AND (@MontStart >= cast(EffectivityDateFrom as datetime) and (cast(EffectivityDateTo as datetime)>=@MontStart or (cast(EffectivityDateTo as datetime)<=@MonthEnd OR EffectivityDateTo is null)))
order by EmployeeName

But this query returns also those employees who resigned in the previous months and years.

Here is the result.

NULL value of EffectivityDateTo column means that employee is still employed (Status=1).

Status 1 = Employed/Active.
Status 0 = Inactive
Employee 901790 is still active though his EmployedTo is year 2010, he is still set as Active

EmployeeNo  |  EmployeeName    |  Status  |   EmployedFrom |  EmployedTo
901790      |  EmpName1        |  1       |   2008-07-28   |    2010-07-31  
902566      |  EmpName2        |  1       |   2013-01-25   |    2013-12-13
902502      |  EmpName3        |  1       |   2012-08-15   |    NULL 
902309      |  EmpName4        |  0       |   2011-07-12   |    2015-08-14
902575      |  EmpName5        |  0       |   2013-03-11   |    2015-08-14
902706      |  EmpName6        |  1       |   2014-03-24   |    2015-10-10

Expected result is this:

EmployeeNo  |  EmployeeName    |  Status  |   EmployedFrom |  EmployedTo
902502      |  EmpName3        |  1       |   2012-08-15   |    NULL 
902309      |  EmpName4        |  0       |   2011-07-12   |    2015-08-14
902575      |  EmpName5        |  0       |   2013-03-11   |    2015-08-14
902706      |  EmpName6        |  1       |   2014-03-24   |    2015-10-10

Upvotes: 0

Views: 159

Answers (1)

SouthL
SouthL

Reputation: 175

I think your query could be a lot simpler. I'm not sure what the query would be to get to your first table, but I'll assume that's the Employmenthistory table

SELECT * FROM Employmenthistory 
WHERE 
(Status = 1 AND EmployedFrom <= @MonthEnd)  
--Get all active employees that started before the end of the specific month

OR (status = 0 AND EmployedTo >= @MonthStart AND EmployedTO <= @MonthEnd)
--Get all employees who stopped working in the given timeframe.

Upvotes: 1

Related Questions