October 5, 2009 at 6:59 am
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
October 5, 2009 at 8:42 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 5, 2009 at 9:54 am
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)
October 5, 2009 at 10:11 am
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
October 5, 2009 at 10:18 am
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).
October 5, 2009 at 10:30 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 5, 2009 at 10:38 am
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
October 5, 2009 at 10:40 pm
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
Change is inevitable... Change for the better is not.
October 7, 2009 at 8:45 am
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
October 7, 2009 at 8:52 am
Jeff Moden (10/5/2009)
Proper calculation of age should never include /365.35. It also doesn't need to be complex...
DECLARE @DOB DATETIMESET @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
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 7, 2009 at 9:24 am
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 DATETIMESET @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
Change is inevitable... Change for the better is not.
October 8, 2009 at 7:24 am
Jeff, that solution for Age in Years is sweet. (I have saved it for future use.)
Thanks 😀
October 8, 2009 at 8:40 am
Aye. Thank you for the feedback. I just wish I was the one that thought of it. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply