All Forums Database
bijit1709 3 posts Joined 10/14
10 Oct 2014
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.

Thanks

Raja_KT 1246 posts Joined 07/09
10 Oct 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

bijit1709 3 posts Joined 10/14
10 Oct 2014

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.

11 Oct 2014

Hi try like this,
count(empl_id) over(partition by empl_id,date order by date rows between 1 preceding and 1 preceding)
 

dnoeth 4628 posts Joined 11/04
11 Oct 2014

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
end 

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.

Dieter

You must sign in to leave a comment.