Teradata equivilent for Oracle's logical window offset

Database

Teradata equivilent for Oracle's logical window offset

Hi folks,

I'm trying to add a column that contains a cumulative count of the number of deliveries preceding each order (or line) in a moving window of 28 days. (Ie - a rolling one month snapshot of a company's activity for each line) 

First I tried to use the aggregate window code BETWEEN CURRENT ROW AND 28 PRECEDING (28 = 1 month), but each day could have more than 1 record or no records.

I thought that something like this would work (green oracle code). But I can't find a way to make the range interval work in teradata.

The only other thing I could think of is to join the table to itself using the dates, but even when I break the task into monthly chunks the results never return as the table is too large (60 m rows +).

Would appraciate any ideas and advice,

T

 Count (delivery_id) OVER (partition by company_name ORDER BY delivery_date) RANGE INTERVAL '28' DAY PRECEDING ) FROM postal_orders

1 REPLY
Enthusiast

Re: Teradata equivilent for Oracle's logical window offset

Hi

UP

i have the same question. How do we specify bounds in analytical windows in range terms. Or how we workaround this.