views:

83

answers:

6

Hello I've been tasked with doing a few queries on a large SQL Server 2000 database.

The query I'm having trouble with is "find the number of people between ages 20 and 40"

How would I do this? My previous query to get a count of everyone looks like this:

select count(rid) from people where ... 

(with the ... being irrelevant conditions). I've googled some but the only thing I've found for calculating age is so large that I don't see how to embed it into a query, or it is a stored procedure which I do not have the permissions to create.

Can someone help me with this?

The relevant part of the people table is like so:

RID(unique key) | dateofbirth(datetime) | firstname....
A: 

If it's datetime, then I found this little article that should get you started

Ólafur Waage
How would you embed that in a query though? Sorry I don't know SQL Servers "deeper" features
Earlz
+4  A: 

Assuming birthday is stored as a DateTime

Select Count(*)
From    (
        Select Id, Floor(DateDiff(d, BirthDate, GetDate()) / 365.25) As Age
        From People
        ) As EmpAges
Where EmpAges Between 20 And 40

This could also be written without the derived table like so:

Select Count(*)
From People
Where Floor(DateDiff(d, BirthDate, GetDate()) / 365.25)  Between 20 And 40

Yet another way would be to use DateAdd. As OMG Ponies and ck mentioned, this one would be the most efficient of the bunch as it would enable the use of an index on dateOfBirth if it existed.

Select Count(*)
From People
Where DateOfBirth Between DateAdd(yy, -40, GetDate()) And DateAdd(yy, -20, GetDate())
Thomas
@OMG Ponies - The first example uses a derived table (EmpAges)
Thomas
+1: The last one is the best, because it would take advantage of an index on the `dateofbirth` column, assuming one existed. And there would only be one pass over the table...
OMG Ponies
The bottom on is the only good answer, you should remove the top part as it will be a thousand times slower.
ck
I ended up using the second method. Speed didn't matter since it was a run-once query.
Earlz
@ck, @OMG Ponies, since the OP used the second variant, I amended the post to state that the last was the most efficient.
Thomas
+1  A: 
select count(*)
from YourTable
where dateofbirth >= '1970-05-24' and dateofbirth <= '1990-05-24'

Adjust the dates according to the current date.

Jakob
Wow actually that kinda makes me laugh. It's such a hack but that would work
Earlz
Problem is, if this is something that needs to be run on a semi-regular basis you'd be constantly updating your string.
taylonr
Sure, it's not the most persistent thing (and I wouldn't use it blindly over the other ideas here :P), but it gets the job done without having to rely on built-in functions and the like. Also, if you're producing the query from a programming language, you can most certainly get the current date from that environment. Just though it'd be a nice suggestion, to show that things doesn't have to be elaborate all the time :)
Jakob
A: 

You could do something like: YEAR(GETDATE()) - YEAR(dateOfBirth) > 20

taylonr
A: 

I'm assuming the people table has birth dates in it.

Rough pseudo code.

Select DateDiff(YY, people.DOB. Datetime.NOW) as age
from people
where age....
nportelli
+1  A: 

You should compute the dates that form the boundaries of your range, and then use those dates.

DECLARE @Today datetime, @StartDate datetime, @EndDate datetime

SET @Today = DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)
SET @StartDate = DateAdd(dd, 1, DateAdd(yy, -40, @Today))
SET @EndDate = DateAdd(yy, -20, @Today)

SELECT *
FROM People
WHERE DateOfBirth BETWEEN @StartDate AND @EndDate

This gives you a query where you have a chance at using an index.

David B
This is the best performing answer.
ck