Count Age Groups - 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

General SQL Server Forums

New to SQL Server Programming

Count Age Groups

Author

Topic

Dale45039
Starting Member

41 Posts

Posted - 2012-08-29 : 11:57:54

I'm trying to count groups of ages for a specific date range. Age calculation based on upper end of the Appt_Acknowledged_Date range (EG: in the code below, that would be '2012-07-11 23:59:59').

Desired Column Results:
COL01:Appt_Sched_Location_Descr, COL02:TotalAge0-2Years, COL03:TotalAge3-4Years, COL04:TotalAge5-6Years, COL05:TotalAge7-10Years, COL06:TotalAge11-18Years, COL07:TotalAge19-24Years.

Desired Row Results:
One row for each "Appt_Sched_Location_Descr"

===============================================================
I can get the info I need by changing the date ranges manually.
this is the code I used:
===============================================================
Select
vwGenPatApptInfo.Appt_Sched_Location_Descr,
Count(Distinct vwGenPatApptInfo.Patient_ID) As "Age-0-2"
From
vwGenPatApptInfo vwGenPatApptInfo Inner Join
vwGenPatInfo vwGenPatInfo On vwGenPatInfo.Patient_ID =
vwGenPatApptInfo.Patient_ID
Where
vwGenPatApptInfo.Appt_Acknowledged_Date Between '2011-07-11 00:01:01' And
'2012-07-11 23:59:59' And vwGenPatInfo.Patient_DOB Between '2010-07-11 00:00:01' And '2012-07-11 23:59:59'
Group By
vwGenPatApptInfo.Appt_Sched_Location_Descr
Order By
Count(Distinct vwGenPatApptInfo.Patient_ID) Desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-29 : 12:45:03

[code]
Select
vwGenPatApptInfo.Appt_Sched_Location_Descr,
Count(Distinct case when case when month(vwGenPatInfo.Patient_DOB)>MONTH(getdate())
or (month(vwGenPatInfo.Patient_DOB)=MONTH(getdate()) and day(vwGenPatInfo.Patient_DOB)> day(getdate()))
then datediff(yy,vwGenPatInfo.Patient_DOB ,getdate())-1
else datediff(yy,vwGenPatInfo.Patient_DOB ,getdate())
end <= 2 then vwGenPatApptInfo.Patient_ID end) As "Age-0-2",
Count(Distinct case when case when month(vwGenPatInfo.Patient_DOB)>MONTH(getdate())
or (month(vwGenPatInfo.Patient_DOB)=MONTH(getdate()) and day(vwGenPatInfo.Patient_DOB)> day(getdate()))
then datediff(yy,vwGenPatInfo.Patient_DOB ,getdate())-1
else datediff(yy,vwGenPatInfo.Patient_DOB ,getdate())
end between 3 and 4 then vwGenPatApptInfo.Patient_ID end) As "Age-3-4",
.....
Count(Distinct case when case when month(vwGenPatInfo.Patient_DOB)>MONTH(getdate())
or (month(vwGenPatInfo.Patient_DOB)=MONTH(getdate()) and day(vwGenPatInfo.Patient_DOB)> day(getdate()))
then datediff(yy,vwGenPatInfo.Patient_DOB ,getdate())-1
else datediff(yy,vwGenPatInfo.Patient_DOB ,getdate())
end between 19 and 24 then vwGenPatApptInfo.Patient_ID end) As "Age-19-24"
From
vwGenPatApptInfo vwGenPatApptInfo Inner Join
vwGenPatInfo vwGenPatInfo On vwGenPatInfo.Patient_ID =
vwGenPatApptInfo.Patient_ID
Where
vwGenPatApptInfo.Appt_Acknowledged_Date Between '2011-07-11 00:01:01' And
'2012-07-11 23:59:59'
Group By
vwGenPatApptInfo.Appt_Sched_Location_Descr
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dale45039
Starting Member

41 Posts

Posted - 2012-08-29 : 13:52:27

Exellent - works like a charm. Thank you visakh16!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-29 : 14:33:14

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dale45039
Starting Member

41 Posts

Posted - 2012-08-29 : 15:30:47

visakh16, what class(es) do you recommend for beginners?
what book(s) do you refer to the most?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-29 : 15:53:14

quote:
Originally posted by Dale45039

visakh16, what class(es) do you recommend for beginners?
what book(s) do you refer to the most?

I dont use books much

i mostly refer sites like

http://www.w3schools.com/sql/default.asp

to start off and then use BOL to further understand on syntax options

Any class you take unless you practise it doesnt give you a value add
so i dont believe in taking classes as such

You can always come to forums like these and see questions answers to learn lot of cool things (thats what I did and gained the most, thanks to geeks here)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources