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)

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)

• sql
• group-by
• count
• teradata
• teradata-sql-assistant

2 Answers 2

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

i don't think Teradata has a DATEDIFF function.

try instead e.g.:

MONTHS_BETWEEN(d.birthdate,d.min_claimdate)

link to documentation of MONTHS_BETWEEN fn: https://docs.teradata/r/Enterprise_IntelliFlex_VMware/SQL-Date-and-Time-Functions-and-Expressions/DateTime-and-Interval-Functions-and-Expressions/MONTHS_BETWEEN/MONTHS_BETWEEN-Syntax