Advanced SQL - Group By With Cube

Need some direction on getting these example results:

I need to group by 2-3 different attributes, one of them a calculated value off of a birthdate attribute, but I want to know whether they are over or under a certain age. I don't want to restrict the result set by age, but rather subgroup results by age ranges.

Simplified Ex:
SELECT class, time, count(nameID) as Total
from students
group by class, time
with cube

Ex RS:
class time >15 Total
-- -- -- --
Math 9:30 true 17
Math 9:30 false 8
PE 7:45 true 5
PE 7:45 false 10

Hopefully you get the idea. I can not figure out how to get the evaluated boolean of whether or not thier age is >15.

It is important that result set be a cube to calculate all the total and subgroup totals. I don't even know if this is possible in my case though.

All help greatly appreciated.

SlezakFirst, assuming you are using SQL Server 2000, you need to create a UDF which will calculate the age. You can find one here: http://www.databasejournal.com/img/UDF_GetAge.sql

Next, you can make use of this function in your query:

SELECT class, time, CASE WHEN dbo.GetAge(DateOfBirth,GETDATE()) > 15 THEN 'True' ELSE 'False' END AS '>15', count(nameID) as Total
from students
group by class, time, dbo.GetAge(DateOfBirth,GETDATE())
with cube

Terri|||That was pretty much what I wanted. Thank you greatly!

I did want to group by 'CASE WHEN dbo.GetAge(DateOfBirth,GETDATE()) > 15 THEN 'True' ELSE 'False' END' and not by 'dbo.GetAge(DateOfBirth,GETDATE())', but I was able to figure that out. :p

Much obliged, and I appreciate that GetAge function too.

Slezak