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?
  • 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, 2012 at 19:04

2 Answers 2


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
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:

    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
        datediff(day, dateofbirth, current_timestamp) / 365.25 as YearsOld
        dbo.Person p
    a.Name as AgeCategory
    people p
    cross apply
        select top 1
            dbo.AgeCategory ac
            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.

  • Since the categories will be rather constant (I think), the overlap problem poses little risk. It is an important note, though. Nov 12, 2012 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. :-) Nov 12, 2012 at 14:24
  • @ Kevin Feasel .... are not there any simpler other ways than that? Nov 19, 2012 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. Nov 19, 2012 at 12:45
  • @ Kevin Feasel thanx alot for your time and your answer Nov 20, 2012 at 3:45

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
    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'

i.e. For this table:

    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:



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, 
        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;
  • You might also want to have a trigger that executes similar logic, for all inserts after the column is added. Nov 19, 2012 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, 2012 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. Nov 19, 2012 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. Nov 19, 2012 at 18:54
  • 1
    Oracle equivalent: sqlfiddle.com/#!4/4480c/1 Nov 19, 2012 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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