Age Stats Report Help Please

[Keith Hubert]

We have a DOB field in a MYSQL table.

We would like to show in a report, the number of members in our club, whose age is between 65-74, 75-84 and 85+.

Your suggestions for the report and syntax would be appreciated.

[coleresources]

Re: Age Stats Report Help Please

Is it a date field that the DOB is stored in?

[coleresources]

Re: Age Stats Report Help Please

Assuming it is a date field. You can do something like this in your select statement to get the age of the person.

I would create a view and add the below to the select statement to give you the age

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), '%Y')+0 AS age FROM YourTableorView

Then you can do a simple filter expression to get the data you need from your age ranges.

[Keith Hubert]

Re: Age Stats Report Help Please

Hi Josh,

Thanks for your replies. Yes the DOB is a date field.

If we use the SELECT statement as you have it, will that enable us to get the number of members in the age ranges shown above on the same report?

[coleresources]

Re: Age Stats Report Help Please

It's a start. It gives you the age of the person you need in the format you need it in.

Then you could do something like this. Create a calculated column in your report for each of your age ranges and I think something like this would work.

Calculated Column 1: Expression=between_num(convert_type(age,"C"),65,74) then set the calculated column to provide the COUNT of the records when it prompts you
Calculated Column 2: Expression=between_num(convert_type(age,"C"),75,84) then set the calculated column to provide the COUNT of the records when it prompts you
Calculated Column 3. Expression=AGE >= 85 then set the calculated column to provide the COUNT of the records when it prompts you

Normally I would place this type of information in a group header or footer of a report but I am unsure of your exact requirements.

[Keith Hubert]

Re: Age Stats Report Help Please

Josh,

Thanks for this, will give a try and let you know.

[doula]

Re: Age Stats Report Help Please

Josh .............

I'm working on this with Keith. Thanks for your efforts. It is not getting us what we need unfortunately.

Here is our Select Statement (which get the same results as your Select statement above:)

SELECT SCLL_MEM_ID, DOB, YearsBetweenDates(DOB, CurrentDate() ) AS Age, STATUS FROM scll_members_tbl

This gets:

"Members Id" : "DOB" : "Age" (in years) : and : "Status" (Membership type)

There are 12 records in the query.

I have tried some Calculated Values

Age_Range_65_74 = between(age,65,74) This produeces T or F

Count_1 = count(calc->Age_Range_65_74 =.T.) I was hopeing this count count the T

cnt_calc_Count_1_for_Grand = count(calc->Count_1,grp->Grand,grp->Grand) Have put this in the Footer and results in 1 when it should be 4

I had hoped that getting the age in years from the select statement would enable a Between function to get just the records with age in the 65 - 74 range but no luck.

We either get 0, 1 or 12 not the 4 that meet the criteria.

Would be grateful for any further thoughts.


Thanks for your help.

John

[glenschild]

Re: Age Stats Report Help Please

Try

TIMESTAMPDIFF(YEAR,DOB,CURDATE()) AS age

Cheers

Glen

[coleresources]

Re: Age Stats Report Help Please

Hey John...What if you do a

between_num(convert_type(age,"C"),65,74) = .t. and then select it to gather the count...Does that work?

[doula]

Re: Age Stats Report Help Please

Josh ............

Thank for your suggestions. They work to a point.

Our Select statement is getting us the Age in years using the 2 suggestions:

SELECT SCLL_MEM_ID, DOB, YearsBetweenDates(DOB, CurrentDate() ) AS Age, STATUS FROM scll_members_tbl

and

TIMESTAMPDIFF(YEAR,DOB,CURDATE()) AS age

Using Between in a calculated expression returns all 12 records in the query. We know only 4 meet the criteria.

Age_65_74_2 = If(Age>=65.AND.Age<=74,.T.,.F.) returns .T. or .F.

As we can get .T. or .F. results we should be able to do a count of the .T. records but we are not having much sucess.

We have tried a Count of .T. as a calculated expression Count_Age_65_74_2 = Count(calc->Age_65_74_2,GRP->detail)=.T but this returns just .F.

In the footer we tried cnt_calc_count_1_for_Grand = count(calc->count_1,grp->Grand,grp->detail) This returns all 12.

Are we missing something?

Any further thoughts would be gratefully recieved.

Thanks

John

[GGandhi]

Re: Age Stats Report Help Please

SELECT scll_mem_id, dob, YearsBetweenDates(dob, CurrentDate() ) AS age, status
FROM scll_members_tbl
WHERE YearsBetweenDates(dob, CurrentDate() ) > 65 AND YearsBetweenDates(dob, CurrentDate() ) < 74

[Keith Hubert]

Re: Age Stats Report Help Please

Hi Govindan,

SELECT scll_mem_id, dob, YearsBetweenDates(dob, CurrentDate() ) AS age, status
FROM scll_members_tbl
WHERE YearsBetweenDates(dob, CurrentDate() ) > 65 AND YearsBetweenDates(dob, CurrentDate() ) < 74

Many thanks for this bit of code.

The question that still remains, is how to get the count of number of members in each age range.

[Michael1954]

Re: Age Stats Report Help Please

Keith

You can produce a view that will give all the Info you want for your report.

If you use Case When Then syntax you can display it as one row or 3 depending on your requirements.

If you send me the data you want to return and the table structure I will write the view.


Michael

You can give me a ring on 01323 895337 if you want.

[GGandhi]

Re: Age Stats Report Help Please

SELECT 
COUNT(*),
CASE
	WHEN age >=60 AND age <= 70 THEN '60 - 70'
	WHEN age >=71 AND age <= 80 THEN '70 - 80'
	WHEN age >=81 AND age <= 90 THEN '80 - 90'
	END AS ageBand
FROM
(SELECT TimestampDiff(YEAR, dob, CurDate())
AS age
FROM scll_members_tbl) AS t1
GROUP BY ageBand

you need to setup a subquery to do what you want.
adjust age the way you want.
this will give you the count and the age band
I do not know a method that gives individual record then totals it.

this is native syntax, not portable alpha build.

different layout method

SELECT
	SUM(IF(age BETWEEN 60 AND 70,1,0)) AS '60 - 70',
	SUM(IF(age BETWEEN 71 AND 80,1,0)) AS '70 - 80',
	SUM(IF(age BETWEEN 81 AND 90,1,0)) AS '80 - 90'
FROM
(SELECT TimestampDiff(YEAR, dob, CurDate())
AS age
FROM scll_members_tbl) AS t1

will give you tabular columns.

[GGandhi]

Re: Age Stats Report Help Please

I do not know a method that gives individual record then totals it.

that was then, not anymore.
you can actually list all members in a certain age bracket then give a total count on that group as group total.