| Average a Date? [message #243] |
Thu, 31 January 2002 15:05  |
John Carr
Messages: 7
Registered: January 2002
|
Junior Member |
|
|
Hi,
I want to calculate the average age of patients on a year by year basis. How do you average a date in oracle?
I have the birthdate stored as a DATE field, and the range I want to average will be set by a juliandate I stored as a Number in the encounter table, as shown below:
Demographics (
PatientId VARCHar2, <pk>
DATEBIRTH DATE
)
Encounter (
PatientId Varchar2 <pk>
Encounternum Varchar2 <pk>
DayNUm Varchar2 <pk>
Juliandate NUMBER
)
Here is some code I wrote that I thought would work, but Oracle says I can’t use average
select avg(d.datebirth), to_number(substr(to_char(e.juliandate), 1, 2))
from demographics d, encounter e
where d.patienid in
(select distinct patientid
from encounter
where juliandate between 78000 and 78999)
and e.patientid = d.patientid
group by to_number(substr(to_char(e.juliandate), 1, 2))
What Should I DO?! I am stuck.
Thanks
John
|
|
|
|
| Re: Average a Date? [message #250 is a reply to message #243] |
Fri, 01 February 2002 00:26  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
To get the average age you have first of all have to get their age. To do this you have subtract the birthdate from the current date. Because the time unit in oracle is the day you have then to divide the result by 365.25. You can use this result to calculate the average age.
avg((sysdate-d.datebirth)/365.25)
HTH
Mike
|
|
|
|
| Re: Average a Date? [message #264 is a reply to message #250] |
Fri, 01 February 2002 12:47 |
John Carr
Messages: 7
Registered: January 2002
|
Junior Member |
|
|
|
Thanks. However, if this is the sysdate, this is the age as of 2002. I want to calculate their age in 1975 (for example) and then average all the values to come up with the avergae age of the patient in the clinic for 1975.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
