Please Help: How would you perform loops in Teradata

Teradata Applications
Tourist

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
 

 

  • Recursive
  • Stored Procedure

Accepted Solutions
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
2 REPLIES
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

Highlighted
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