sum last n days quantity using sql window function

Question

I am trying to create following logic in Alteryx and data is coming from Exasol database.

Column “Sum_Qty_28_days“ should sum up the values of “Qty ” column for same article which falls under last 28 days.

My sample data looks like:

and I want following output:

E.g. “Sum_Qty_28_days” value for “article” = ‘A’ and date = ‘’2019-10-8” is 8 because it is summing up the “Qty” values associated with dates (coming within previous 28 days) Which are: 2019-09-15 2019-10-05 2019-10-08 for “article” = ‘A’.

Is this possible using SQL window function? I tried myself with following code:

SUM("Qty") OVER (PARTITION BY "article", date_trunc('month',"Date")
             ORDER BY "Date")

But, it is far from what I need. It is summing up the Qty for dates falling in same month. However, I need to sum of Qty for last 28 days.

Thanks in advance.

Sample data is better presented as [formatted text](https://meta.stackoverflow.com/a/251362). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking tables. — , Dec 09 '19 at 13:02

score 1 · Answer 1 · answered Dec 09 '19 at 12:57

1

Yes, this is possible using standard SQL and in many databases. However, this will not work in all databases:

select t.*,
       sum(qty) over (partition by article
                      order by date
                      range between interval '27 day' preceding and current row
                     ) as sum_qty_28_days
from t;

answered Dec 09 '19 at 12:57

Gordon Linoff

1,242,037
58
646
786

Hi Gordon, thanks for the solution. My data is coming from Exasol database, and I am using Alteryx Designer to implement the logic. When I am trying with your solution it is giving me error "Error: Formula In-DB (5): Error SQLPrepare: [EXASOL][EXASolution driver]syntax error, unexpected PRECEDING_ [line 3, column 33] (Session: 1652450532585726518)" – Pardeep Naik Dec 09 '19 at 13:57

GMB · Answer 2 · 2021-09-14T15:19:46.107

1

If your RDBMS does not support the range frame, an alternative solution is to use an inline subquery:

select 
    t.*,
    (
        select sum(t1.qty) 
        from mytable t1 
        where 
            t1.article = t.article
            and t1.date between t.date - interval 28 days and t.date
    ) sum_qty_28_days
from mytable t

edited Sep 14 '21 at 15:19

answered Dec 09 '19 at 13:10

GMB

216,147
25
84
135

Shouldn't date in-between range be referenced to "t" table instead of "t1"? – jwzinserl Nov 25 '20 at 01:59

sum last n days quantity using sql window function

2 Answers2

Linked