Database Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print All Forums Database Heats 5 posts Joined 02/15 11 Feb 2015 Future commitment by week Hi guys, My first post here (I've used other threads to help solve problems but stuck and haven't found a solution so looking for a method or hint). I have a table that has commitment in it (qty of units coming in against future weeks); imagine like so;   ```Week Product Commitment 1 A 10 1 B 10 2 A 10 2 B 10 3 A 10 4 B 10 5 A 10 ```   There are gaps in the data so above we have no delivery of product A in week 4 or B in week 3 and 5.     The above example is limited by 5 weeks but the actual data will go into the future approximately a year, I am looking for a method to create an output by Product for every week that sums up all future commitment of that product.  At week 1 we are expecting 40 total future deliveries of Product A; 40 = 10 (wk 1) + 10 (wk 2) + 10 (wk 3) + 10 (wk 5).  In the same way in week 2 we expect 30 total future deliveries; 30 =10 (wk 2) + 10 (wk 3) + 10 (wk 5) and so on. Essentially the results would probably appear as a crossjoin between week and product. i.e. the output for the above would be;   ```Product Week Total Future Commitment for this week A 1 40 A 2 30 A 3 20 A 4 10 A 5 10 B 1 30 B 2 20 B 3 10 B 4 10 B 5 0 ```  This could then be cleaned to remove 0 data as it should mean there is no future deliveries from above. I am unsure how to go about this other than potentially doing a load of case statements but I'm sure there must be an easier way? Any help or pointers would be greatly appreciated - I believe figuring things out is the best form of learning but pretty stumped right now. Rob Heats 5 posts Joined 02/15 11 Feb 2015 First Table   Week            Product           Commitment 1                      A                    10 1                      B                    10 2                      A                    10 2                      B                    10 3                      A                    10 4                      B                    10 5                      A                    10   Second Table   Product           Week             Total Future Commitment for this week A                       1                                     40 A                       2                                     30 A                       3                                     20 A                       4                                     10 A                       5                                     10 B                       1                                     40 B                       2                                     30 B                       3                                     20 B                       4                                     20 B                       5                                     10   dnoeth 4628 posts Joined 11/04 11 Feb 2015 What's the actual data type of week, numeric or date? On how many weeks/products will it be calculated? Dieter Heats 5 posts Joined 02/15 16 Feb 2015 Hi Dieter, Apologies for the delay in getting back to you (turned on email notifications now).  The Week number is actually the Date of Week End so maybe it is possible to include all future dates. Please let me know if there is any other information needed and as a side note when originally posting this the data realligned itself, hence the second post (has it been corrected and if so how do you paste tablular data in). Many thanks  Heats dnoeth 4628 posts Joined 11/04 16 Feb 2015 Hi Heats, if week is already a date it's much easier. Sum all the future commitments using an OLAP function and the add the missing rows using EXPAND ON: ```CREATE VOLATILE TABLE vt(Week DATE, Product CHAR, Commitment INT) ON COMMIT PRESERVE ROWS; INS vt(DATE '2015-01-04' + (1*7) ,'A', 10); -- assuming week are from monday to sunday INS vt(DATE '2015-01-04' + (1*7) ,'B', 10); INS vt(DATE '2015-01-04' + (2*7) ,'A', 10); INS vt(DATE '2015-01-04' + (2*7) ,'B', 10); INS vt(DATE '2015-01-04' + (3*7) ,'A', 10); INS vt(DATE '2015-01-04' + (4*7) ,'B', 10); INS vt(DATE '2015-01-04' + (5*7) ,'A', 10); SELECT product, future_commitment, BEGIN(pd) AS week, pd, week FROM ( SELECT product, week, /* SUM(commitment) OVER (PARTITION BY product ORDER BY week ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS future_commitment, */ -- more complicated than the previous, but now both sum(commitment) and min(week) -- use the same PARTITION/ORDER/ROWS which results in a single step in Explain commitment + COALESCE(SUM(commitment) OVER (PARTITION BY product ORDER BY week ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING), 0) AS future_commitment, MIN(week) OVER (PARTITION BY product ORDER BY week ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS next_week -- next row's sunday FROM vt ) AS dt EXPAND ON PERIOD(week, COALESCE(next_week-1, week+6)) AS pd BY ANCHOR SUNDAY -- one row per week ORDER BY 1,3``` Dieter Heats 5 posts Joined 02/15 16 Feb 2015 Thanks Dieter, Going to take me some time to work through and digest this (I want to understand whats going on rather than just using it). Really appreciate your help and hopefully be able to report back soon somewhat wiser :) Heats Heats 5 posts Joined 02/15 16 Feb 2015 Works perfectly :) amended it to fit the data and had some minor issues but got round them. Thank you very much for your Help! You must sign in to leave a comment. Active Posters