Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table_name (BRANCH, PERIOD, QTY) AS
SELECT 105, 319, 17 FROM DUAL UNION ALL
SELECT 105, 320, 20 FROM DUAL UNION ALL
SELECT 105, 321, 32 FROM DUAL UNION ALL
SELECT 105, 322, 61 FROM DUAL UNION ALL
SELECT 107, 319, 17 FROM DUAL UNION ALL
SELECT 107, 321, 18 FROM DUAL UNION ALL
SELECT 107, 322, 16 FROM DUAL UNION ALL
SELECT 108, 319, 21 FROM DUAL UNION ALL
SELECT 108, 322, 27 FROM DUAL;
9 rows affected

SELECT branch,
       period,
       qty,
       COALESCE(prev_period_1_qty, 0) AS prev_period_1_qty,
       COALESCE(prev_period_2_qty, 0) AS prev_period_2_qty,
       COALESCE(prev_period_3_qty, 0) AS prev_period_3_qty
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY branch
  ORDER     BY period DESC
  MEASURES
    curr.period AS period,
    curr.qty    AS qty,
    prev1.qty   AS prev_period_1_qty,
    prev2.qty   AS prev_period_2_qty,
    prev3.qty   AS prev_period_3_qty
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO NEXT ROW
  PATTERN (curr prev1? prev2? prev3?)
  DEFINE
    prev1 AS curr.period - 1 = period,
    prev2 AS curr.period - 2 = period,
    prev3 AS curr.period - 3 = period
)
ORDER BY branch, period


BRANCH
PERIOD
QTY
PREV_PERIOD_1_QTY
PREV_PERIOD_2_QTY
PREV_PERIOD_3_QTY


105
319
17
0
0
0

105
320
20
17
0
0

105
321
32
20
17
0

105
322
61
32
20
17

107
319
17
0
0
0

107
321
18
0
17
0

107
322
16
18
0
17

108
319
21
0
0
0

108
322
27
0
0
21


SELECT branch,
       period,
       qty,
       CASE
       WHEN p1 = period - 1
       THEN q1 ELSE 0
       END AS prev_period_1_qty,
       CASE
       WHEN p1 = period - 2 THEN q1
       WHEN p2 = period - 2 THEN q2
       ELSE 0
       END AS prev_period_2_qty,
       CASE
       WHEN p1 = period - 3 THEN q1
       WHEN p2 = period - 3 THEN q2
       WHEN p3 = period - 3 THEN q3
       ELSE 0
       END AS prev_period_3_qty
FROM   (
  SELECT t.*,
         LAG(period, 1) OVER (PARTITION BY branch ORDER BY period) AS p1,
         LAG(period, 2) OVER (PARTITION BY branch ORDER BY period) AS p2,
         LAG(period, 3) OVER (PARTITION BY branch ORDER BY period) AS p3,
         LAG(qty, 1, 0) OVER (PARTITION BY branch ORDER BY period) AS q1,
         LAG(qty, 2, 0) OVER (PARTITION BY branch ORDER BY period) AS q2,
         LAG(qty, 3, 0) OVER (PARTITION BY branch ORDER BY period) AS q3
  FROM   table_name t
)


BRANCH
PERIOD
QTY
PREV_PERIOD_1_QTY
PREV_PERIOD_2_QTY
PREV_PERIOD_3_QTY


105
319
17
0
0
0

105
320
20
17
0
0

105
321
32
20
17
0

105
322
61
32
20
17

107
319
17
0
0
0

107
321
18
0
17
0

107
322
16
18
0
17

108
319
21
0
0
0

108
322
27
0
0
21

BRANCH	PERIOD	QTY	PREV_PERIOD_1_QTY	PREV_PERIOD_2_QTY	PREV_PERIOD_3_QTY
105	319	17	0	0	0
105	320	20	17	0	0
105	321	32	20	17	0
105	322	61	32	20	17
107	319	17	0	0	0
107	321	18	0	17	0
107	322	16	18	0	17
108	319	21	0	0	0
108	322	27	0	0	21

BRANCH	PERIOD	QTY	PREV_PERIOD_1_QTY	PREV_PERIOD_2_QTY	PREV_PERIOD_3_QTY
105	319	17	0	0	0
105	320	20	17	0	0
105	321	32	20	17	0
105	322	61	32	20	17
107	319	17	0	0	0
107	321	18	0	17	0
107	322	16	18	0	17
108	319	21	0	0	0
108	322	27	0	0	21

BRANCH	PERIOD	QTY	PREV_PERIOD_1_QTY	PREV_PERIOD_2_QTY	PREV_PERIOD_3_QTY
105	319	17	0	0	0
105	320	20	17	0	0
105	321	32	20	17	0
105	322	61	32	20	17
107	319	17	0	0	0
107	321	18	0	17	0
107	322	16	18	0	17
108	319	21	0	0	0
108	322	27	0	0	21

BRANCH	PERIOD	QTY	PREV_PERIOD_1_QTY	PREV_PERIOD_2_QTY	PREV_PERIOD_3_QTY
105	319	17	0	0	0
105	320	20	17	0	0
105	321	32	20	17	0
105	322	61	32	20	17
107	319	17	0	0	0
107	321	18	0	17	0
107	322	16	18	0	17
108	319	21	0	0	0
108	322	27	0	0	21

BRANCH	PERIOD	QTY	PREV_PERIOD_1_QTY	PREV_PERIOD_2_QTY	PREV_PERIOD_3_QTY
105	319	17	0	0	0
105	320	20	17	0	0
105	321	32	20	17	0
105	322	61	32	20	17
107	319	17	0	0	0
107	321	18	0	17	0
107	322	16	18	0	17
108	319	21	0	0	0
108	322	27	0	0	21

BRANCH	PERIOD	QTY	PREV_PERIOD_1_QTY	PREV_PERIOD_2_QTY	PREV_PERIOD_3_QTY
105	319	17	0	0	0
105	320	20	17	0	0
105	321	32	20	17	0
105	322	61	32	20	17
107	319	17	0	0	0
107	321	18	0	17	0
107	322	16	18	0	17
108	319	21	0	0	0
108	322	27	0	0	21