Solved! Go to Solution.
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
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
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
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.
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?
Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.
The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.