CODEWITHSUNDEEP
sqlsubquerydate-arithmeticalteryxexasol
Pardeep Naik
Pardeep Naik

Reputation: 109

sum last n days quantity using sql window function

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:

enter image description here

and I want following output:

enter image description here

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.

Upvotes: 0

Views: 3782

Answers (2)

GMB
GMB

Reputation: 222432

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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;

Upvotes: 1

Related Questions