Age categorisation

[ashtrik203]

Hello,

I want to create a new category of age bands called age at service. 

What I already have is age, and services date. I want to create the age at service and then categories them in categories of 5 years. 

I did the following: patientage-(Age(Today(),service_date)) as age_at_service

This gives me the right age at service. Then I created categories:

if(age_at_service <=4, '0-4',
if(age_at_service <=9, '5-9',
if(age_at_service <=14, '10-14',
if(age_at_service <=19, '15-19',
if(age_at_service <=24, '20-24',
if(age_at_service <=29, '25-29',
if(age_at_service <=34, '30-34',
if(age_at_service <=39, '35-39',
if(age_at_service <=44, '40-44',
if(age_at_service <=49, '45-49',
if(age_at_service <=54, '50-54',
if(age_at_service <=59, '55-59',
if(age_at_service <=64, '60-64',
if(age_at_service <=69, '65-69',
if(age_at_service <=74, '70-74',
if(age_at_service <=79, '75-79','80+')))))))))))))))) as age_at_service_cat;

Now the problem I am facing is that a person can have multiple services date. Which means they can move across categories in the age at service categories through years. Now  when this is the case, a person is counted multiple times in one year even though he appeared only in one category/age in that year. 

This is how it looks:

So here, the individual is 41 in2017 so only that should be 1, the rest should be blank, for 2018 42 should be 1 and the rest blank and so on and so forth.

I have tried everything and have found no soultion

Any help would be appreciated. 

Thanks 

[BrunPierre]

Perhaps categorizing as follows;

IF(age_at_service <=4, '0-4',
IF(age_at_service >4 and age_at_service <=9, '5-9',
IF(age_at_service >9 and (age_at_service <=14, '10-14',
IF(age_at_service >14 and age_at_service <=19, '15-19',....................
IF(age_at_service >74 and age_at_service <=79, '75-79','80+')))))))))))))))) as age_at_service_cat;

[vinieme12]

base your evaluations on either MAX(service_date)   or Min(service_date)  , check with your business user which one they would prefer