July 04, 2009

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

0 Responses to "Select Range of Records Based on a Condition in SQL Server 2005/2008"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions