Teradata Applications

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Teradata
- :
- Product Forums
- :
- Teradata Applications
- :
- Re: Please Help: How would you perform loops in Te...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-21-2017
04:47 AM

03-21-2017
04:47 AM

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 -

Solved! Go to Solution.

Labels:

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-03-2017
02:56 AM

04-03-2017
02:56 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-03-2017
02:56 AM

04-03-2017
02:56 AM

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
##
##### Re: Please Help: How would you perform loops in Teradata

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-03-2017
06:37 AM

04-03-2017
06:37 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-04-2017
06:04 AM

05-04-2017
06:04 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-04-2017
08:05 AM

05-04-2017
08:05 AM

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.