Displaying Multiple Results

Question

Post reply

Displaying Multiple Results

rachish1915

SSC Journeyman

Points: 81
More actions
December 12, 2008 at 1:59 pm

#216408

I have a query displays the following results for ages 6 and under, gender, and dob.
OwnerOrganizationID MaleFemale Gender Unknown DOB Unknown
136 273231 3 0
137 79 91 1 0
Is it possible for the same query to display other age groups as well, such as 6-13.
Here's the query:
Declare @minage as int
set @minage = 0
Declare @maxage as int
set @maxage = 5
Declare @Male varchar (1)
set @Male = 'M'
Declare @Female varchar (1)
set @Female = 'F'
Declare @GenderUnknown varchar (1)
set @GenderUnknown = ' '
Declare @DOBUnknown datetime
set @DOBUnknown = ' '
select
m.OwnerOrganizationID,
sum(case isnull(m.SEX,'') when @Male then 1 else 0 end) as Male,
sum(case isnull(m.SEX,'') when @Female then 1 else 0 end) as Female,
sum(case isnull(m.SEX,'') when @GenderUnknown then 1 else 0 end) as 'Gender Unknown',
sum(case isnull(m.birthdate,'') when @DOBUnknown then 1 else 0 end) as 'DOB Unknown'
From
Members m
inner join mastermemberindex mmi on m.family_duid = mmi.diouniqueid
where mmi.status = 1 and --m.memberstatus = 'Active' and
(datediff (year, convert (datetime, m.birthdate), getdate())) >=@minage and
(datediff (year, convert (datetime, m.birthdate), getdate())) <=@maxage
group by m.OwnerOrganizationid
order by m.OwnerOrganizationID asc

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Have you looked at the Having clause as used in Group By statements? If not read in BOL

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/463c5e18-f274-474f-9861-1010fe3757b8.htm

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ray M SSC-Insane Points: 21093 More actions · Answer 2

Depends on how you want it to look, but you can certainly do it like this.

Declare @minage as int

set @minage = 0

Declare @maxage as int

set @maxage = 13 -- 5 changed to get more kidz

Declare @Male varchar (1)

set @Male = 'M'

Declare @Female varchar (1)

set @Female = 'F'

Declare @GenderUnknown varchar (1)

set @GenderUnknown = ' '

Declare @DOBUnknown datetime

set @DOBUnknown = ' '

select

m.OwnerOrganizationID,

sum(case isnull(m.SEX,'') when @Male then 1 else 0 end) as Male,

sum(case isnull(m.SEX,'') when @Female then 1 else 0 end) as Female,

sum(case isnull(m.SEX,'') when @GenderUnknown then 1 else 0 end) as 'Gender Unknown',

sum(case isnull(m.birthdate,'') when @DOBUnknown then 1 else 0 end) as 'DOB Unknown',

case

when (datediff (year, convert (datetime, m.birthdate), getdate())) between 0 and 5

then 'UnderSix'

when (datediff (year, convert (datetime, m.birthdate), getdate())) between 6 and 13

then 'SixThruThirteen'

end) as [AgeGroup]

From

Members m

inner join mastermemberindex mmi on m.family_duid = mmi.diouniqueid

where mmi.status = 1 and --m.memberstatus = 'Active' and

-- The age of the kids is limited here, change to be larger

(datediff (year, convert (datetime, m.birthdate), getdate())) >=@minage and

(datediff (year, convert (datetime, m.birthdate), getdate())) <=@maxage

group by m.OwnerOrganizationid,