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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook