age profile - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

age profile

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-03-22 : 13:51:34

I need to do a select that gives me something like this..

AGE GROUP | MALES | FEMALES
less than 30 | 900 | 367
30-40 | 456 | 299
41-50 | 70 | 90

The followowing sql gives me this data:
SELECT dbo.tblDIMHREmployee.HREmpEmployeeNameFirst, dbo.tblDIMHREmployee.HREmpEmployeeNameLast,
dbo.tblDIMHREmployee.HREmpEmployeeBirthDate, dbo.tblDIMEmployeeGender.EmpGenGenderName
FROM dbo.tblDIMHREmployee INNER JOIN
dbo.tblDIMEmployeeGender ON dbo.tblDIMHREmployee.HREmpEmployeeGender = dbo.tblDIMEmployeeGender.EmpGenGenderCode

Charles Mott 11/29/1930 Male
Stanley Yarosewick 9/10/1939 Male
Nancy Newkerk 9/10/1928 Female
Clarence Murphy 4/20/1934 Male
Joy Vandever 1/1/1933 Female
Fredric Roux 7/26/1938 Male
Thomas Goodrich 11/5/1927 Male
Ralph Miller 1/21/1936 Male

DClayton77
Starting Member

11 Posts

Posted - 2005-03-22 : 14:12:49

Hi,

If you alter you original select statement to include one extra field giving the required age group it can be done quite easily.

To add the age group simply add something like this to the field list of the select statement:


CASE WHEN Year(Now) - Year(HREmpEmployeeBirthDate) < 30 THEN 'Less than 30' ELSE Year(Now) - Year(HREmpEmployeeBirthDate) BETWEEN 30 AND 40 THEN '30 - 40' END AS [AgeGroup]

This effectively gives you a column containing their age group.

Use the finished query from above as a sub query and create something like the following:


SELECT

     AgeGroup
     , SUM(CASE WHEN EmpGenGenderName = 'Male' THEN 1 ELSE 0 END) AS Males
     , SUM(CASE WHEN EmpGenGenderName = 'Female' THEN 1 ELSE 0 END) AS Females

FROM [...Query from above...]

GROUP BY AgeGroup

This is building your own columns on the fly. It is doing a count by saying if they are in this group and are male give a 1 else give a 0. Summing that gives you a count. It sounds confusing but it does work. This method is really effective if you have very large tables.

Hope this helps

-------------------------------------------

You know, if there's one thing I've learned from being in the army, it's never ignore a pooh-pooh. I knew a major: got pooh-poohed; made the mistake of ignoring the pooh-pooh -- he pooh-poohed it. Fatal error, because it turned out all along that the soldier who pooh-poohed him had been pooh-poohing a lot of other officers, who pooh-poohed their pooh-poohs. In the end, we had to disband the regiment -- morale totally destroyed ... by pooh-pooh!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-22 : 14:57:51

A temporary table can help to do "dynamic groups"
DClayton, I think that your query will be incorrect depending on if the person has or hasn't had his birthday already in the year.

(I haven't tried the following, gotafly, see if it works...)

[CODE]DECLARE @agegroups TABLE(grp VARCHAR(10), yrsfrom INT, yrsto INT)
INSERT @agegroups(grp,dysfrom,dysto)
SELECT '<30', 0, 29
SELECT '30-40', 30, 40
SELECT '41-50', 41, 50
SELECT '>50', 51, 400

SELECT
ag.grp AS [AGE GROUP]
,SUM(CASE WHEN eg.EmpGenGenderCode = 'Male' THEN 1 ELSE 0 END) AS MALES
,SUM(CASE WHEN eg.EmpGenGenderCode = 'Female' THEN 1 ELSE 0 END) AS FEMALES
FROM
dbo.tblDIMHREmployee e
INNER JOIN dbo.tblDIMEmployeeGender eg
ON e.HREmpEmployeeGender = eg.EmpGenGenderCode
INNER JOIN @agegroups ag
ON DATEDIFF(year,e.HREmpEmployeeBirthDate,GETDATE())+(1+SIGN(DATEDIFF(minute,'20050524',GETDATE())))/2
BETWEEN ag.yrsfrom AND ag.yrsto
GROUP BY
ag.grp
ORDER BY
ag.yrsfrom[/CODE]

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-22 : 15:01:02

Didn't we do this already?

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-22 : 15:08:30

yup with our norwegian friend Lumbago and his zip codes.
now where's that thread???
EDIT: here ya go:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45960

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-22 : 15:14:17

>> Didn't we do this already?
Like a hundred times probably, but the search page is not working

Having a déja vu ?

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-22 : 15:18:35

quote:
Originally posted by rockmoose

>> [i]Having a déja vu ?

Not while I'm working...Perhaps a Margarita when I get home though....

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-22 : 15:26:24

*LOL*
Get a life guys...
I already finished work, just relaxing writing some SQL etc.

Yeah, tuesday, time to go to the kitchen to get a

...
As if there were any left

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-22 : 15:33:39

[code]
declare @BeersLeft decimal(12,2)

select @BeersLeft = count(beer) from Fridge

if @BeersLeft = 0.00
begin
select 'Bang head on the wall'
select money from vallet
use store
select top 6 beer from Store_Fridge
select dbo.PayBeer(6)
insert into bag(beerCount) values (6)
use Appartment
insert into Fridge(beer)
select beer from store..Bag
select top 1 beer from firdge where coldEnough=1
end

select 'drink beer!!! YAAAAYY!!!!!'
[/code]

Go with the flow & have fun! Else fight the flow

DClayton77
Starting Member

11 Posts

Posted - 2005-03-22 : 15:58:58

spirit1: *LOL*

rockmoose: good call (on the solution and the beer). Table variables are so cool and yet i always seem to forget about them!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-22 : 16:24:03

oh spirit, oh spirit on the team
please, please beam me a sixpack o' zlatorog's

Cheers & welcome Clayton.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources