I'm trying to create a frequency count table on a derived healthcare table of member age in months when receiving a first medical diagnosis (datediff(month,d.birthdate,d.min_claimdate)).
My cleaned up Teradata SQL Assistant code below - when I run I'm getting the following error:
"SELECT. [3706] Syntax error: expected something between '(' and the 'month' keyword."
What am I missing?
select datediff(month,d.birthdate,d.min_claimdate) as age_months, count(datediff(month,d.birthdate,d.min_claimdate)) as cnt
from (
select a.member_id, c.birthdate, b.diagnosis_code, min(b.claimdate) as min_claimdate
from
table_A a
join
table_B b
on a.claim_id=b.claim_id
left join
table_C c
on a.member_id=c.member_id
group by 1,2,3
) as d
group by datediff(month,d.birthdate,d.min_claimdate)
order by datediff(month,d.birthdate,d.min_claimdate)
Exactly the same result as T-SQL's datediff(month,d.birthdate,d.min_claimdate) is returned by Standard SQL's d.min_claimdate - d.birthdate MONTH(4) (number of month boundaries crossed). The data type is INTERVAL MONTH but you can easily CAST((d.min_claimdate - d.birthdate MONTH(4)) AS SmallInt).
MONTHS_BETWEEN(d.birthdate,d.min_claimdate) returns a different result, the number of full months, e.g. '2025-03-17' - '2023-02-20' returns 0 instead of 1. The data type is NUMBER with a very high precision, you probably want do cast to integer, too.
Btw, Teradata allows reusing aliases:
select CAST((d.min_claimdate - d.birthdate MONTH(4)) AS SmallInt) as age_months
...
group by age_months
order by age_months