CODEWITHSUNDEEP
sqlteradatawindow-functions
lex0
lex0

Reputation: 101

Using LAG after a SUM over PARTITION

+---------+----------+---------+------------+-------+---------+---------------+
| Product | Version  | Country |    Week    | sales | returns | Running_sales |
+---------+----------+---------+------------+-------+---------+---------------+
| Pdt1    | pdt1ver1 | Aus     | 2020M01W01 |    10 |       3 |             7 |
| pdt1    | pdt1ver1 | Fra     | 2020M01W01 |     8 |       2 |             6 |
| pdt1    | pdt1ver1 | Fra     | 2020M01W02 |    15 |       5 |            16 |
| pdt1    | pdt1ver2 | UK      | 2020M01W01 |    20 |       5 |            15 |
| pdt1    | pdt1ver2 | UK      | 2020M01W02 |    15 |       1 |            29 |
| pdt1    | pdt1ver2 | UK      | 2020M01w03 |     9 |       0 |            38 |
| pdt2    | pdt2ver1 | Fra     | 2020M01W01 |     5 |       1 |             4 |
| pdt2    | pdt2ver1 | Fra     | 2020M01W02 |     3 |       0 |             7 |
+---------+----------+---------+------------+-------+---------+---------------+

I have a view giving me the data and running total shown before. It's a simple select, with also this in the select statement (already got help from here for this one):

Sum(sales-returns) over(
  partition by Version, Country 
  Order by Week 
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) Running_sales

I now need to have another column that would give me the returns of the previous week (but staying in the subgroup it should be in, this means I can't have anything in row 1, 2, 4 and 7 because there is no previous row), as such :

+---------+----------+---------+------------+-------+---------+---------------+------------------+
| Product | Version  | Country |    Week    | sales | returns | Running_sales | previous_returns |
+---------+----------+---------+------------+-------+---------+---------------+------------------+
| Pdt1    | pdt1ver1 | Aus     | 2020M01W01 |    10 |       3 |             7 |                  |
| pdt1    | pdt1ver1 | Fra     | 2020M01W01 |     8 |       2 |             6 |                  |
| pdt1    | pdt1ver1 | Fra     | 2020M01W02 |    15 |       5 |            16 |                2 |
| pdt1    | pdt1ver2 | UK      | 2020M01W01 |    20 |       5 |            15 |                  |
| pdt1    | pdt1ver2 | UK      | 2020M01W02 |    15 |       1 |            29 |                5 |
| pdt1    | pdt1ver2 | UK      | 2020M01w03 |     9 |       0 |            38 |                1 |
| pdt2    | pdt2ver1 | Fra     | 2020M01W01 |     5 |       1 |             4 |                  |
| pdt2    | pdt2ver1 | Fra     | 2020M01W02 |     3 |       0 |             7 |                1 |
+---------+----------+---------+------------+-------+---------+---------------+------------------+

I tried adding this to my select statement :

LAG(returns,1,0)
    OVER(partition by Version, Country 
      Order by Week 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) previous_returns

But it's obviously not that easy. Maybe the window ? Do I need to use partition again ? Any tips would be appreciated on this issue. I'm using Teradata.

Upvotes: 1

Views: 1277

Answers (1)

GMB
GMB

Reputation: 222382

This should as simple as:

lag(returns) over(partition by version, country order by week) previous_returns

Upvotes: 1

Related Questions