Select Range of Records Based on a Condition in SQL Server 2005/2008

In one of the reports, my clients wanted to retrieve a list of Employees who take Sick Leaves. The requirement was to retrieve Employees falling in 40% to 60% range of all Employees who take Sick Leaves. I found the HumanResources.Employee table of the AdventureWorks database to be a good test base to show you the query for the same.

Here’s the query to retrieve records falling in a range

SELECT EmployeeID, Title, Gender, SickLeaveHours
FROM HumanResources.Employee
WHERE SickLeaveHours IN
(
    SELECT TOP 60 PERCENT SickLeaveHours FROM HumanResources.Employee
    ORDER BY SickLeaveHours
)
AND SickLeaveHours NOT IN
(
    SELECT TOP 40 PERCENT SickLeaveHours FROM HumanResources.Employee
    ORDER BY SickLeaveHours
)

The result of running this query is that data for 54 Employees out of 290 Employees are retrieved who fall in between 40 to 60% of all Employee who take sick leaves