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,
Count (delivery_id) OVER (partition by company_name ORDER BY delivery_date) RANGE INTERVAL '28' DAY PRECEDING ) FROM postal_orders
i have the same question. How do we specify bounds in analytical windows in range terms. Or how we workaround this.