Group count on derived table in Teradata

Question

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)

dnoeth · Accepted Answer · 2025-03-17 18:45:09Z

3

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

answered Mar 17 at 18:45

dnoeth

60.8k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mechanical_meat · Accepted Answer · 2025-03-17 16:12:33Z

1

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.com/r/Enterprise_IntelliFlex_VMware/SQL-Date-and-Time-Functions-and-Expressions/DateTime-and-Interval-Functions-and-Expressions/MONTHS_BETWEEN/MONTHS_BETWEEN-Syntax

answered Mar 17 at 16:12

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

Collectives™ on Stack Overflow

Group count on derived table in Teradata

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related