sp help pls - 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)

sp help pls

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2007-07-13 : 13:54:46

Requirement:
create a report that produces statistcal data based on age groups and gender.
Query1: The agegroups need to be show a count of agegroups (see results) based on dateOfVisit - DateOfBirth to determine age
Query2: The gender needs to show how many (count) persons were male and how many female

example tables:
tblVisits:
VisitID PersonID DateOfVisit
1 2 12\1\06
2 3 6\2\07
3 4 4\3\07
4 2 2\2\07
5 5 2\3\07

tblPerson
PersonID DateOfBirth gender
2 11\1\02 Male
3 5\5\84 Female
4 2\12\32 Male
5 1\8\82 Female

Results AgeCategory:
AgeUnder5 5-11yrs 12-19 20-64 65Over
1 1 0 2 1

Results GenderCategory:
GenderMaleCount GenderFemaleCount
2 2

I have no idea on how to do this - can anyone help pls? Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-13 : 14:12:33

sounds like a homework question? Can you provide what you have tried so far? and what errors, if any, you have seen so far.. so we can guide you better..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ann
Posting Yak Master

220 Posts

Posted - 2007-07-13 : 14:47:28

I don't know what you mean by "homework", but anyway, I started with query 2 (gender) since I thought it would be easier ... this is what I got, but it's not right:

SELECT count(p.Gender) as Gender
FROM tblPerson as p
Left Join tblVisits as t
on p.patient_id = t.patient_id
Group By p.Gender

Results:
5282
98
5624

As you can see, I have 3 rows, but I should have only 2 genders! LOL!

ann
Posting Yak Master

220 Posts

Posted - 2007-07-13 : 15:13:09

ok - I have query 2 figured out:

SELECT gender, count(p.Gender) as genderCount
FROM tblPerson as p
Left Join tblPerson as t
on p.patient_id = t.patient_id
where gender != 'U'
Group By p.Gender

It seems they had another "gender" type in there....

So for query 1 (grouping by age), how do I do a calculation to determine age? Can anyone point me in the right direction - thanks

ann
Posting Yak Master

220 Posts

Posted - 2007-07-13 : 16:16:57

on query 2 - determining age - this is what I have so far:

SELECT DATEDIFF(day, DateOfBirth, dateOfVisit)/365 AS age
FROM tblPerson
Left Join tblVisits
on tblPerson.patientid = tblVisits.patientid

but I need to do a count on the age groups based on different parameters (example under5, 65+, etc.,)

Can anyone help with that?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-13 : 17:10:24

Here's a couple of links to calculate age:

www.windowsitpro.com/Article/ArticleID/14267/14267.html
http://classicasp.aspfaq.com/date-time-routines-manipulation/given-two-dates-how-do-i-determine-an-age.html

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-13 : 17:29:37

This function does an age calculation taking things like leap year into account.

Age Function F_AGE_IN_YEARS:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462


select
	[No Visits]	= sum(case when a.VisitID is null then 1 else 0 end),
	[Age Under5]	= sum(case when a.AgeAtVisit < 5 then 1 else 0 end),
	[Age 5-11]	= sum(case when a.AgeAtVisit between  5 and 10 then 1 else 0 end),
	[Age 12-19]	= sum(case when a.AgeAtVisit between 12 and 19 then 1 else 0 end),
	[Age 20-64]	= sum(case when a.AgeAtVisit between 20 and 64 then 1 else 0 end),
	[Age 65orOver]	= sum(case when a.AgeAtVisit > 64 then 1 else 0 end)
from
	(
	select
		AgeAtVisit = dbo.F_AGE_IN_YEARS( p.DateofBirth, t.DateOfVisit),
		t.VisitID 
	from
		tblPerson as p
		Left Join
		tblVisits as t
		on p.patient_id = t.patient_id
	) a

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources