Case statement with aggregating

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

SQL Server Development (2000)

Case statement with aggregating

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2008-01-29 : 14:42:01

I am trying to agregate data within my case statement - but am having trouble getting my case statement to work and have no idea how I would aggregate once I can get it to work!
Here is my statement:


SELECT DATEDIFF(day, Date_Birth, date_of_service)/365 AS age, 
     CASE 
      WHEN age < 5 THEN 'UnderFive'
      WHEN age >= 5 and age < 12 THEN 'FiveEleven'
      WHEN age >= 12 and age < 20 THEN 'TeenAge'
      WHEN age >= 20 and age < 65 THEN 'MidAge'
      WHEN age >= 65 THEN 'OldAge'
   END AS "AgeCategory", gender

FROM Patients 

where date_of_service is not null 
and date_of_service >= '07/01/1999' AND date_of_service <= '07/31/1999'

So what I need to know is how many patients under 5, how many between 5 - 12, etc.,

Can anyone please PLEASE help me with this?? It is driving me crazy!

Thanks

chorofonfilo
Starting Member

40 Posts

Posted - 2008-01-29 : 16:06:27

I dont have this clear, do you wanna return a value on your function that will be later agreggated(as your code shows), or do you wanna obtain the number of patients processing each birthdate and the actual date and aggregate it on another table?.
I would suggest you to use a function like the one you are ussing which will receive the value of the actual day field, and the birth date field of the actual registry ussing a select,but rescuing each returned value into a variable (ussing this same select command),and then you would perform an update command with the actual id of that patient right after the execution of the select to insert the value caught by the variable.
If what you want is counting according the age ranges, you can use acumulators(@a=@a+1) on each case of the select when command, which will count one by one each patient into the age intervals, after all the registries are processed you will have the number of patients for each case on each variable and then you can perform an insert into another table in which you need to have that information stored.

Let me know if this help you.

Perseverance worths it...:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 16:20:19

Please read this article how to calculate age, and why the formula you use is flawed.
http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 16:27:02

[code]SELECT e.AgeCategory,
COUNT(*)
FROM (
SELECT CASE
WHEN d.Age < 5 THEN 'Under five'
WHEN d.Age BETWEEN 5 AND 11 THEN 'Five eleven'
WHEN d.Age BETWEEN 12 AND 19 THEN 'Teen age'
WHEN d.Age BETWEEN 20 AND 64 THEN 'Mid age'
ELSE 'Old age'
END AS AgeCategory
FROM (
SELECT dbo.fnYearsApart(Date_Birth, Date_Of_Service) AS Age
FROM Patients
WHERE Date_Of_Service >= '19990701'
AND Date_Of_Service < '19990801'
) AS d
) AS e
GROUP BY e.AgeCategory[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources