Future commitment by week

Database
Enthusiast

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

6 REPLIES
Enthusiast

Re: Future commitment by week

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


Senior Apprentice

Re: Future commitment by week

What's the actual data type of week, numeric or date?

On how many weeks/products will it be calculated?

Enthusiast

Re: Future commitment by week

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

Senior Apprentice

Re: Future commitment by week

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

Enthusiast

Re: Future commitment by week

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

Enthusiast

Re: Future commitment by week

Works perfectly :) amended it to fit the data and had some minor issues but got round them.

Thank you very much for your Help!