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

ADSI OPENQUERY and MultiValued Attributes

Is there any way to retrieve AD MultiValued Attributes using T-SQL?
These are the messages you get when you try:
Could not get the data of the row from the OLE DB provider 'ADSDSOObject'.
Could not convert the data value due to reasons other than sign mismatch or
overflow.
Data status returned from the provider:
[COLUMN_NAME=member STATUS=DBSTATUS_E_CANTCONVERTVALUE],
JBHi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know how to query
MultiValued attributes in Active Directory. If I have misunderstood your
concern, please feel free to point it out.
Based on my knowledge, there is a limitation on querying the Active
Directory which is that multivalued properties cannot be returned in the
result set to SQL Server. ADSI will read schema information from the LDAP
server that defines the structure and syntax of the classes and attributes
used by the server. If the attribute that is requested from the LDAP server
is defined in the schema as being multi-valued it cannot be returned in an
OPENQUERY statement.
It is typical for a directory server to enforce a server limitation on the
number of objects that will be returned for a given query. This is to
prevent denial-of-service attacks and network overloading.
Using ADSI as OLE DB Provider, it can participate in Distributed Query for
following possible scenarios:
- Joining Active Directory objects with SQL Server data.
- Updating SQL data from Active Directory objects.
- Creating three-way or four-way joins with other OLE DB Providers. For
example, Index Server, SQL Server, and Active Directory.
For more detailed information, please refer the article below
Distributed Query
http://msdn.microsoft.com/library/d...-us/adsi/adsi/d
istributed_query.asp
OLE DB Provider for Microsoft Directory Services
http://msdn.microsoft.com/library/d...-us/acdata/ac_8
_qd_12_94fn.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael - Thank you for taking the time to reply to my question. I needed
such confirmation before start using DTS/VBScript to populate a set of
columns...
--
Juan B
"Michael Cheng [MSFT]" wrote:

> Hi,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you would like to know how to query
> MultiValued attributes in Active Directory. If I have misunderstood your
> concern, please feel free to point it out.
> Based on my knowledge, there is a limitation on querying the Active
> Directory which is that multivalued properties cannot be returned in the
> result set to SQL Server. ADSI will read schema information from the LDAP
> server that defines the structure and syntax of the classes and attributes
> used by the server. If the attribute that is requested from the LDAP serve
r
> is defined in the schema as being multi-valued it cannot be returned in an
> OPENQUERY statement.
> It is typical for a directory server to enforce a server limitation on the
> number of objects that will be returned for a given query. This is to
> prevent denial-of-service attacks and network overloading.
> Using ADSI as OLE DB Provider, it can participate in Distributed Query for
> following possible scenarios:
> - Joining Active Directory objects with SQL Server data.
> - Updating SQL data from Active Directory objects.
> - Creating three-way or four-way joins with other OLE DB Providers. For
> example, Index Server, SQL Server, and Active Directory.
> For more detailed information, please refer the article below
> Distributed Query
> [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/d[/ur
l]
> istributed_query.asp
> OLE DB Provider for Microsoft Directory Services
> [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8[/ur
l]
> _qd_12_94fn.asp
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Juan,
You are welcome!
If you have any questions or concerns on SQL Server next time, don't
hesitate to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sorry to Jump on the thread but I am receiving the same issue while querying
the "Description" field from Active Directory. Is this too MULTIVALUE?
Many Thanks
Andy