Hello to all,

I'm in trouble

I've prepared a sql statement extracting the typical ranges of dates
from a table of users: 20-25 years old, 25-30 etc.
After, I've realized that the range is not precise. In fact I use the
YEAR and CURDATE(): this way, persons born in 1980 figure out as 30-
years old, but if - in example - we've a person born in december 1980,
*now* this person will be 29 and not 30 years old.

So, I must use also months, and to be perfect, also days.

This is the sql, I'm in trouble to find a way to transform the
statement in the right way. Any help will be very appreciated! Thanks
in advance

$sql = " (SELECT
SUM(CASE WHEN YEAR(data_nascita) BETWEEN YEAR(CURDATE())-25 AND
YEAR(CURDATE())-21 THEN 1 ELSE 0 END) AS 20_25anni,
SUM(CASE WHEN YEAR(data_nascita) BETWEEN YEAR(CURDATE())-30 AND
YEAR(CURDATE())-26 THEN 1 ELSE 0 END) AS 25_30anni,
SUM(CASE WHEN YEAR(data_nascita) BETWEEN YEAR(CURDATE())-35 AND
YEAR(CURDATE())-31 THEN 1 ELSE 0 END) AS 30_35anni,
SUM(CASE WHEN YEAR(data_nascita) BETWEEN YEAR(CURDATE())-40 AND
YEAR(CURDATE())-36 THEN 1 ELSE 0 END) AS 35_40anni,
SUM(CASE WHEN YEAR(data_nascita) BETWEEN YEAR(CURDATE())-45 AND
YEAR(CURDATE())-41 THEN 1 ELSE 0 END) AS 40_45anni,
SUM(CASE WHEN YEAR(data_nascita) <= YEAR(CURDATE())-46 THEN 1
ELSE 0 END) AS over_45
FROM wd_utenti
WHERE attivo='1')";

Luk

On 2010-07-06 18:37, Luk wrote:

Have you tried the date_sub function?

select
count(case when data_nascita
between date_sub(curdate(), interval '25' YEAR)
and date_sub(curdate(), interval '21' YEAR)
then 1 end) as 20_25anni,
count(case when data_nascita
between date_sub(curdate(), interval '30' YEAR)
and date_sub(curdate(), interval '26' YEAR)
then 1 end) as 25_30anni,
...
from luk;


/Lennart

Luk:

Hi Luk!


Perhaps use:

WHEN NOW()> DATE_ADD(data_nascita, INTERVAL 20 YEARS) AND
NOW()<=DATE_ADD(data_nascita, INTERVAL 25 YEARS)
THEN 1 ELSE 0


--
Erick

Luk:

Hi Luk!


Perhaps use:

WHEN NOW()> DATE_ADD(data_nascita, INTERVAL 20 YEAR) AND
NOW()<=DATE_ADD(data_nascita, INTERVAL 25 YEAR)
THEN 1 ELSE 0


--
Erick

On 6 Lug, 20:12, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m>
wrote:
Many Thanks Erick, your solution worked! :-)

Luk