Analytic function


Analytic function

Hi everyone,

What function can i use to to compare current row data eg: empl_id if exist in last month.

If this month empl_id existed in last month count only once, without using joins.

Its somethings like selecting the two months and getting distinct.

I am selecting current month but want to compare to last month.


Re: Analytic function

It looks like you need rows between preceding or following. With data, it will be more clear.

Re: Analytic function

I am trying to use something like what oracle has

count(empl_id) over(partition by empl_id order by date rows between interval "1" month preceding and "1" month preceding)

With no luck since its not compatible with teradata.


Re: Analytic function

Hi try like this,

count(empl_id) over(partition by empl_id,date order by date rows between 1 preceding and 1 preceding)

Junior Contributor

Re: Analytic function

I assume you ment range between interval '1' month preceding and '1' month preceding instead of rows. The RANGE keyword is not implemented in Teradata (probably due to possible performance problems).

Your query returns 1 when there's exactly one month between the current and the previous row, so you might try to rewrite:

case when add_months(dt,-1)
= min(dt)
over (partition by empl_id
order by dt
rows between 1 preceding and 1 preceding)
then 1
else 0

I don't know exactly how Oracle treats the '1' month for end-of-months dates, maybe you have to switch to oAdd_months instead.