Age Banding (for a beginner, please!)

  • Hi, my first post on here so 😀

    I have done a number of SQL courses over the last decade or so, but never had the opportunity to get stuck into it. At last, my job is changing so that I can start using it - so I am soo looking forward to the 3-day SSRS course later this month.

    But in the meantime, I am trying to create a report that counts the number of people in a particular age band. E.g. How many do we have in the 17-24 age range, etc.

    As I said, I am only on the first rung of the SQL ladder, so any explanations would have to be quite simple, but even a pointer in the direction would be useful. My manager is much more experienced - but I'd like the chance to have a go, before asking for her help!

    Thanks

    Simon

  • It would be easier to supply an example if you posted some more information as is mentioned in article referenced the first link in my signature.

    Are you writing the SQL query as well as the report?

    I would do something like this in the query:

    SELECT

    COUNT(*) AS no_employees,

    CASE

    WHEN DATEDIFF(YEAR, BirthDate, GETDATE()) BETWEEN 17 AND 24 THEN '17-24'

    WHEN DATEDIFF(YEAR, BirthDate, GETDATE()) BETWEEN 25 AND 34 THEN '25-34'

    WHEN DATEDIFF(YEAR, BirthDate, GETDATE()) BETWEEN 35 AND 44 THEN '35-44'

    ELSE '45 AND Older'

    END AS age_group

    FROM

    HumanResources.Employee AS E

    GROUP BY

    CASE

    WHEN DATEDIFF(YEAR, BirthDate, GETDATE()) BETWEEN 17 AND 24 THEN '17-24'

    WHEN DATEDIFF(YEAR, BirthDate, GETDATE()) BETWEEN 25 AND 34 THEN '25-34'

    WHEN DATEDIFF(YEAR, BirthDate, GETDATE()) BETWEEN 35 AND 44 THEN '35-44'

    ELSE '45 AND Older'

    END

    ORDER BY

    age_group

    The CASE statement is the key piece. You could add a calculated column to the dataset in the report and do the similar thing.

  • it does fall down if your birthday is this year but hasn't happened yet

    select DATEDIFF(year,'1981-12-04',getdate())

    will say 28 when they will still be 27

    admittedly, its probably safe to say that the following has its down falls as well but this is what i use.

    select floor(datediff(day, '1981-12-04', GETDATE()) / 365.25)

  • Thanks Jack.

    I'll read the etiquette tonight when I get home (and get the kids to bed!).

    I'll study your answer more closely tomorrow.

    Cheers,

    Simon

  • Thanks David.

    Having just read a few threads about age, I'm glad to say that it isnt a problem for me as the database I get my data from already works the age out itself! It's also made a little easier in that the report only has to be run once a year, as at 31st March (for a fiscal year end).

  • davidandrews13 (10/5/2009)


    it does fall down if your birthday is this year but hasn't happened yet

    select DATEDIFF(year,'1981-12-04',getdate())

    will say 28 when they will still be 27

    admittedly, its probably safe to say that the following has its down falls as well but this is what i use.

    select floor(datediff(day, '1981-12-04', GETDATE()) / 365.25)

    Yeah, I probably could have/should have explained that, but I was trying to keep it simple and it is also why I said that's where I'd start. There are several ways to get it "right", but I felt that they were a little complex to try to explain for a beginner and since the manager was mentioned as being more skilled, I thought I'd just give a starting point.

  • Function F_AGE_IN_YEARS in the script in the link below calculates age in years from @START_DATE through @END_DATE and returns the age as an integer.

    Age Function F_AGE_IN_YEARS

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

  • Proper calculation of age should never include /365.35. It also doesn't need to be complex...

    DECLARE @DOB DATETIME

    SET @DOB = '2008-03-01'

    DECLARE @Now DATETIME

    SET @Now = '2009-03-01'

    --===== This is the proper way to do it for years

    SELECT CASE

    WHEN DATEADD(yy, DATEDIFF(yy, @DOB, @Now) , @DOB) > @Now

    THEN DATEDIFF(yy, @DOB, @Now) - 1

    ELSE DATEDIFF(yy, @DOB, @Now)

    END

    You could easily sub GETDATE() for @Now in the SELECT.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I found a good age calculator, but it is written in Crystal Reports syntax. But the concept is that you have compare the year, then the month, then the day. Not sure how to write in SQL.

    PatientVistProcs.DateofServiceFrom is the date I am comparing to (i.e., current date?), PatientProfile.Birthdate is client's Date of Birth.

    // Convert Date of Birth to Age in Years

    // Get the number of years

    // Correct value only if birthday has already occurred

    // this year

    numberVar ageyrs := Year ({PatientVisitProcs.DateOfServiceFrom}) - Year({PatientProfile.Birthdate});

    // Determine if month has already passed

    numberVar agemo := Month ({PatientProfile.Birthdate}) - Month({PatientVisitProcs.DateOfServiceFrom});

    // Determine if day in month has already passed

    numberVar ageday := Day ({PatientProfile.Birthdate}) - Day({PatientVisitProcs.DateOfServiceFrom});

    // Subtract a year if birthday has not occurred yet

    // this year

    If agemo > 0 OR ( (agemo = 0) and (ageday > 0) )

    then ageyrs := ageyrs -1

    else ageyrs := Truncate(ageyrs);

    ageyrs := Truncate(ageyrs);

    ageyrs

  • Jeff Moden (10/5/2009)


    Proper calculation of age should never include /365.35. It also doesn't need to be complex...

    DECLARE @DOB DATETIME

    SET @DOB = '2008-03-01'

    DECLARE @Now DATETIME

    SET @Now = '2009-03-01'

    --===== This is the proper way to do it for years

    SELECT CASE

    WHEN DATEADD(yy, DATEDIFF(yy, @DOB, @Now) , @DOB) > @Now

    THEN DATEDIFF(yy, @DOB, @Now) - 1

    ELSE DATEDIFF(yy, @DOB, @Now)

    END

    You could easily sub GETDATE() for @Now in the SELECT.

    As usual a nice solution Jeff. I need to think out of box or maybe get in the box.:w00t: One or the other anyway.

  • Jack Corbett (10/7/2009)


    Jeff Moden (10/5/2009)


    Proper calculation of age should never include /365.35. It also doesn't need to be complex...

    DECLARE @DOB DATETIME

    SET @DOB = '2008-03-01'

    DECLARE @Now DATETIME

    SET @Now = '2009-03-01'

    --===== This is the proper way to do it for years

    SELECT CASE

    WHEN DATEADD(yy, DATEDIFF(yy, @DOB, @Now) , @DOB) > @Now

    THEN DATEDIFF(yy, @DOB, @Now) - 1

    ELSE DATEDIFF(yy, @DOB, @Now)

    END

    You could easily sub GETDATE() for @Now in the SELECT.

    As usual a nice solution Jeff. I need to think out of box or maybe get in the box.:w00t: One or the other anyway.

    Heh... sometimes, I don't realize I'm in a box. 😛

    I can't take the credit for this one. I first saw it on a post by Dave Ballantyne and he had gotten it from someplace else. I'm just quick to apply new lessons I've learned so the box doesn't seem so big. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, that solution for Age in Years is sweet. (I have saved it for future use.)

    Thanks 😀

  • Aye. Thank you for the feedback. I just wish I was the one that thought of it. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply