Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to do the following in my DB related to patients:

  • when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients
  • how can I translate the birthday into age category?
  • how to design that age category?
share|improve this question
3  
Today is 2012-11-19. Fatima was born 2011-11-22. She has an appointment today and a followup next week on the 26th. What does your system need to know? Does it need to know that now she's an Infant but when that's re-evaluated next week, she's a toddler? Does it need to record that today's visit was for an Infant whilst the visit next week is for a Toddler? –  billinkc Nov 19 '12 at 19:04
add comment

2 Answers

up vote 9 down vote accepted

Some of this depends upon your particular database implementation. The part that is general, however, is the idea of an age category. Here's one implementation, using ID as a surrogate key and written to work with SQL Server (meaning you would probably need to alter the syntax a little bit to get it to work with another product):

CREATE TABLE dbo.AgeCategory
(
    ID tinyint identity(1,1) NOT NULL,
    MinimumAge tinyint NOT NULL,
    Name varchar(20) NOT NULL
);
ALTER TABLE dbo.AgeCategory ADD CONSTRAINT [PK_AgeCategory] PRIMARY KEY(ID);
CREATE UNIQUE INDEX [IX_MinimumAge] ON dbo.AgeCategory(MinimumAge) INCLUDE(Name);

INSERT INTO dbo.AgeCategory(MinimumAge, Name) values
(0, 'Newborn'),
(1, 'Infant'),
(2, 'Toddler'),
(5, 'Child'),
(12, 'Adolescent'),
(18, 'Adult'),
(30, 'Some marker'),
(40, 'Something else'),
(50, '50+'),
(65, 'Retiree'),
(90, 'Amazing');

I've populated this with sample ages and categories. Here's an example of how you could use this table. I'm going to populate a Person table with dates of birth, and then get a query to find their age category.

Here's the sample data:

CREATE TABLE dbo.Person
(
    ID int identity(1,1) NOT NULL,
    Name varchar(50) NOT NULL,
    DateOfBirth datetime NOT NULL
);

insert into dbo.Person(Name, DateOfBirth) values
('Bob', '1983-01-08'),
('Mary', '1976-05-05'),
('Jane', '1960-04-01'),
('Tony', '2012-08-16'),
('Marcy', '1955-06-11'),
('Carl', '1930-12-24'),
('Joseph', '1918-11-11');

Here's the query:

with people as
(
    select
        Name,
        DateOfBirth,
        datediff(day, dateofbirth, current_timestamp) / 365.25 as YearsOld
    from
        dbo.Person p
)
select
    p.Name,
    p.DateOfBirth,
    p.YearsOld,
    a.MinimumAge,
    a.Name as AgeCategory
from
    people p
    cross apply
    (
        select top 1
            ac.MinimumAge,
            ac.Name
        from 
            dbo.AgeCategory ac
        where
            ac.MinimumAge < p.YearsOld
        order by
            ac.MinimumAge desc      
    ) a;

Note that this query works for SQL Server, but I can't guarantee the syntax will be the same for any other product (or that this is a relatively efficient way to solve this problem for any product other than SQL Server). What we're doing is calculating the age in terms of years based on the date of birth. Then, we can apply that to the age category table using the CROSS APPLY function. This will return the one best age category for a particular row.

Note that you could modify the AgeCategory table to have two age values: MinimumAge and MaximumAge. That would get rid of the cross apply statement and replace it with an INNER JOIN, but the downside there is that you might end up with overlapping rows: if you have a minimum age of 10 and maximum age of 20, and then another row with a minimum age of 15 and a maximum age of 25, you'll have two results for each person between the ages of 15 and 20. The single-entry table gets rid of that problem without having to introduce a trigger or data check other than a unique index on the age column, but might be more costly in terms of performance with large enough data sets.

share|improve this answer
    
Since the categories will be rather constant (I think), the overlap problem poses little risk. It is an important note, though. –  dezso Nov 12 '12 at 14:12
2  
That's a fair point. Roughly half the time I'll do it with the minimum and maximum ages separate (because I know that I'm the only one working defining these categories), but I was feeling all Celko this morning. :-) –  Kevin Feasel Nov 12 '12 at 14:24
    
@ Kevin Feasel .... are not there any simpler other ways than that? –  Hatem Ghazy Nov 19 '12 at 10:06
1  
Alternatively, you could create an AgeCategory table with minimum and maximum ages, use the people CTE to get age in years, and allocate based on WHERE age BETWEEN MinimumAge and MaximumAge. That would be simpler, although leads to the possibility of duplicate rows as I mentioned in my last paragraph. –  Kevin Feasel Nov 19 '12 at 12:45
    
@ Kevin Feasel thanx alot for your time and your answer –  Hatem Ghazy Nov 20 '12 at 3:45
add comment

For SQL SERVER, a very simple way of adding this column is as a computed column, which will be non-deterministic due to the CURRENT_TIMESTAMP dependency.

alter table PATIENT add AgeCategory as
    CASE
    WHEN DateAdd(Year,1,DOB) > Current_Timestamp then 'infant'
    WHEN DateAdd(Year,4,DOB) > Current_Timestamp then 'toddler'
    WHEN DateAdd(Year,10,DOB) > Current_Timestamp then 'children'
    WHEN DateAdd(Year,14,DOB) > Current_Timestamp then 'tween'
    WHEN DateAdd(Year,18,DOB) > Current_Timestamp then 'teenager'
    WHEN DateAdd(Year,65,DOB) > Current_Timestamp then 'adult'
    ELSE 'elderly'
    END;

i.e. For this table:

CREATE TABLE PATIENT
(
    ID integer primary key,
    FullName varchar(255) NOT NULL,
    DOB datetime NOT NULL
);

insert into PATIENT(ID, FullName, DOB) values
    (1, 'John Doe', '1900-01-01'),
    (2, 'Jane Doe', '1960-02-03'),
    (3, 'Joe Public', '1990-05-06'),
    (4, 'Bob the Builder', '2007-08-09'),
    (5, 'Ecco the Dolphin', '2009-12-31'),
    (6, 'Foo', '2012-01-09'),
    (7, 'Bar', '2012-11-11');

The following query:

SELECT * FROM PATIENT

Returns:

ID          FullName             DOB                     AgeCategory
----------- -------------------- ----------------------- -----------
1           John Doe             1900-01-01 00:00:00.000 elderly
2           Jane Doe             1960-02-03 00:00:00.000 adult
3           Joe Public           1990-05-06 00:00:00.000 adult
4           Bob the Builder      2007-08-09 00:00:00.000 children
5           Ecco the Dolphin     2009-12-31 00:00:00.000 toddler
6           Foo                  2012-01-09 00:00:00.000 infant
7           Bar                  2012-11-11 00:00:00.000 infant


For all other RDBMS I would probably recommend going with a view with an additional column, since AgeCategory cannot be persisted - by nature it can change between one select and the next depending on timing. The date manipulation functions are different for each DBMS, but the pattern would be similar.

e.g. Here's the version for SQLite (SQLFiddle)

create view PatientWithAgeCategory as
    select ID, FullName, DOB, 
        CASE
        WHEN Date(DOB, "+1 year") > Current_Timestamp then 'infant'
        WHEN Date(DOB, "+18 year") > Current_Timestamp then 'children'
        WHEN Date(DOB, "+65 year") > Current_Timestamp then 'adult'
        ELSE 'elderly'
        END AgeCategory
    from Patient;
share|improve this answer
    
You might also want to have a trigger that executes similar logic, for all inserts after the column is added. –  FrustratedWithFormsDesigner Nov 19 '12 at 18:50
2  
Why would it need a trigger? Which DBMS are you referring to? A computed column in SQL Server needs no trigger. –  孔夫子 Nov 19 '12 at 18:52
1  
@FrustratedWithFormsDesigner - A trigger wouldn't be suitable as the categorization changes according to current time and date and there may be no inserts that would fire it. –  Martin Smith Nov 19 '12 at 18:53
    
@RichardTheKiwi: I'm not familiar with MS SQL computed columns. I don't think Oracle has computed columns. OP doesn't specify what database they're using. I guess a view with a computed column would be better than an insert trigger; I think that's what I'd use, now that I've given it some thought. –  FrustratedWithFormsDesigner Nov 19 '12 at 18:54
1  
Oracle equivalent: sqlfiddle.com/#!4/4480c/1 –  Leigh Riffel Nov 19 '12 at 20:26
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.