Please Help: How would you perform loops in Teradata

Teradata Applications

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 -
Capture1.PNG
 
 
 
 
 
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 -
Capture2.PNG
 

 


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

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.

 

 

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

Here's an example

 

MaterialPlantCalendar_dateDemandStockDays
M1P17/2/201820501.5
M1P17/3/2018301002.5
M1P17/4/201840501
M1P17/5/201850201
M1P17/6/20182020 
M2P27/2/201830503.2
M2P27/3/201810703
M2P27/4/20180501
M2P27/5/201850201
M2P27/6/20182020 

 

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)