## Please Help: How would you perform loops in Teradata

Teradata Applications
Highlighted
Fan

## Please Help: How would you perform loops in Teradata

We need your help to implement the following scenario through Teradata.
We have to calculate the number of future days for which demand could be satisfied by the stock on a particular day.

Here is the source data -

The stock of 7/2 (50) is enough to satisfy full demand of 7/3 (30) and half demand of 7/4 (20/40=0.5) . (20 remaining after 30 was consumed on 7/3)  giving days: 1 + 0.5 = 1.5

Similarly, the stock of 7/3 is enough to satisfy the full demand of 7/4 (40), full demand of 7/5 (50) and half demand of 7/6 [(100 - (50+40))/20=0.5] giving days = 1+1+0.5 = 2.5

Target Data -

Accepted Solutions
Senior Supporter

## Re: Please Help: How would you perform loops in Teradata

nice SQL puzzel :)
try this:

```CREATE VOLATILE TABLE vt_ts (
report_dt DATE,
demand INTEGER,
stock INTEGER
) UNIQUE PRIMARY INDEX (report_dt) ON COMMIT PRESERVE ROWS;

INSERT INTO vt_ts VALUES ('2018-07-02', 20, 50);
INSERT INTO vt_ts VALUES ('2018-07-03', 30, 100);
INSERT INTO vt_ts VALUES ('2018-07-04', 40, NULL);
INSERT INTO vt_ts VALUES ('2018-07-05', 50, NULL);
INSERT INTO vt_ts VALUES ('2018-07-06', 20, NULL);

WITH RECURSIVE leftover(
report_dt,
ref_dt,
demand,
stock,
stock_available_for_date,
stock_leftover_after_date,
day_fraction,
level
)
AS
(
SELECT
report_dt,
report_dt,
demand,
stock,
stock,
stock,
1.0000,
cast(1 AS INTEGER)
FROM vt_ts
WHERE stock IS NOT NULL
UNION ALL
SELECT
r.report_dt,
n.report_dt                                                              AS ref_dt,
r.demand,
r.stock,
r.stock_leftover_after_date,
CASE WHEN r.stock_leftover_after_date - n.demand < 0
THEN 0
ELSE r.stock_leftover_after_date - n.demand END                          AS leftover,
CASE WHEN leftover > 0
THEN 1
ELSE cast(r.stock_leftover_after_date AS DECIMAL(38, 10)) / n.demand END AS day_fraction,
r.level + 1
FROM vt_ts n
JOIN
leftover r
ON r.ref_dt + 1 = n.report_dt
)
SELECT
report_dt,
demand,
stock,
sum(day_fraction)
OVER (PARTITION BY report_dt
ORDER BY level
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM leftover
WHERE stock_available_for_date > 0
AND level <= 10
qualify LEVEL = 1
UNION ALL
SELECT
report_dt,
demand,
stock,
NULL
FROM vt_ts
WHERE stock IS NULL
ORDER BY 1, 2```

The recursive query calculates how long the specific stock will last.

the union all at the end adds the dates where no stock is in the source table

Have fun

1 ACCEPTED SOLUTION
4 REPLIES
Senior Supporter

## Re: Please Help: How would you perform loops in Teradata

nice SQL puzzel :)
try this:

```CREATE VOLATILE TABLE vt_ts (
report_dt DATE,
demand INTEGER,
stock INTEGER
) UNIQUE PRIMARY INDEX (report_dt) ON COMMIT PRESERVE ROWS;

INSERT INTO vt_ts VALUES ('2018-07-02', 20, 50);
INSERT INTO vt_ts VALUES ('2018-07-03', 30, 100);
INSERT INTO vt_ts VALUES ('2018-07-04', 40, NULL);
INSERT INTO vt_ts VALUES ('2018-07-05', 50, NULL);
INSERT INTO vt_ts VALUES ('2018-07-06', 20, NULL);

WITH RECURSIVE leftover(
report_dt,
ref_dt,
demand,
stock,
stock_available_for_date,
stock_leftover_after_date,
day_fraction,
level
)
AS
(
SELECT
report_dt,
report_dt,
demand,
stock,
stock,
stock,
1.0000,
cast(1 AS INTEGER)
FROM vt_ts
WHERE stock IS NOT NULL
UNION ALL
SELECT
r.report_dt,
n.report_dt                                                              AS ref_dt,
r.demand,
r.stock,
r.stock_leftover_after_date,
CASE WHEN r.stock_leftover_after_date - n.demand < 0
THEN 0
ELSE r.stock_leftover_after_date - n.demand END                          AS leftover,
CASE WHEN leftover > 0
THEN 1
ELSE cast(r.stock_leftover_after_date AS DECIMAL(38, 10)) / n.demand END AS day_fraction,
r.level + 1
FROM vt_ts n
JOIN
leftover r
ON r.ref_dt + 1 = n.report_dt
)
SELECT
report_dt,
demand,
stock,
sum(day_fraction)
OVER (PARTITION BY report_dt
ORDER BY level
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM leftover
WHERE stock_available_for_date > 0
AND level <= 10
qualify LEVEL = 1
UNION ALL
SELECT
report_dt,
demand,
stock,
NULL
FROM vt_ts
WHERE stock IS NULL
ORDER BY 1, 2```

The recursive query calculates how long the specific stock will last.

the union all at the end adds the dates where no stock is in the source table

Have fun

Senior Supporter

## Re: Please Help: How would you perform loops in Teradata

just for completness a sligtly simplified version - removed the union all...

`WITH RECURSIVE leftover(    report_dt,    ref_dt,    demand,    stock,    stock_available _for _date,    stock_leftover _after _date,    day_fraction,    level)AS(  SELECT    report_dt,    report_dt,    demand,    stock,    stock,    stock,    1.0000,    cast(1 AS INTEGER)  FROM vt_ts  UNION ALL  SELECT    r.report_dt,    n.report_dt                                                               AS ref_dt,    r.demand,    r.stock,    r.stock_leftover_after                                                       _date,    CASE WHEN r.stock_leftover_after _date - n.demand < 0      THEN 0    ELSE r.stock_leftover_after _date - n.demand END                          AS leftover,    CASE WHEN leftover > 0      THEN 1    ELSE cast(r.stock_leftover_after _date AS decimal(38, 10)) / n.demand END AS day_fraction,    r.level + 1  FROM vt_ts n    JOIN    leftover r      ON r.ref_dt + 1 = n.report_dt         AND r.stock_available_for _date IS NOT NULL)SELECT  report_dt,  demand,  stock,  sum(day_fraction)  OVER (PARTITION BY report_dt    ORDER BY level    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)FROM leftover     qualify LEVEL = 1`
Fan

## Re: Please Help: How would you perform loops in Teradata

Hi ulrich,

Thanks for your help. We have one more requirement that we have to incorporate this logic with every material and plant combination. Can you please help us with that.

Fan

## Re: Please Help: How would you perform loops in Teradata

Here's an example

 Material Plant Calendar_date Demand Stock Days M1 P1 7/2/2018 20 50 1.5 M1 P1 7/3/2018 30 100 2.5 M1 P1 7/4/2018 40 50 1 M1 P1 7/5/2018 50 20 1 M1 P1 7/6/2018 20 20 M2 P2 7/2/2018 30 50 3.2 M2 P2 7/3/2018 10 70 3 M2 P2 7/4/2018 0 50 1 M2 P2 7/5/2018 50 20 1 M2 P2 7/6/2018 20 20

As you can see in the above table, the logic has to be implemented for every material and plant combination e.g. (M1,P1), (M2,P2) etc... In the query provided by you, it was traversing on all dates for one material and plant only. We have sets of multiple material and plant(about 50k combinations) for which we need to calculate the supply days individually per material, plant and date as illustrated above.

Can you help me in getting the recursive logic including the material and plant combination as well?

Tags (1)