using a calculation done in the previous row in this row's calculation

General

using a calculation done in the previous row in this row's calculation

I am doing a query in which the results of a previous row calculation are used in the next row calculation (recursive I guess).

For example the results should look like this:

Item

Loc

Week

A

B

C

Inventory (Prev Inv + A + B - C)

 

1111

aaaa

201301

100

40

60

80

First time prev inv is null

1111

aaaa

201302

30

200

100

210

 

So I can use

MIN(Inventory) OVER(ORDER BY item, loc, week ROWS BETWEEN 1 PRECEDING AND 1

       PRECEDING) as PrevInv

And the second row will be good (PrevInv + A + B – C)

However on the rest of the rows I cannot use the previous row's created Inventory value.

20 REPLIES
N/A

Re: using a calculation done in the previous row in this row's calculation

Based on your narration you might not need recursion, just a modification of your query:

SUM(a+b-c) OVER (ORDER BY week ROWS UNBOUNDED PRECEDING AS Inventory
Dieter

Re: using a calculation done in the previous row in this row's calculation

Thanks Dieter.  I am always amazed at how quickly and accurately you guys answer posts. 

Unfortunately, I have one more wrinkle... this works fine, but basically I need this inventory to reset with each new item-location combination. 

I have the data ordered by item-location-week.  On the first week for every item location, I calculate inventory for that row a little differently, then I want to do the sum for the remaining weeks of that item-location week.  This works fine for the first item-location, but then on the second item-location I calculate the first week, but the sum just continues from the previous item location.

case when CUST_WEEK = POS.POSMINWK

then -- first week

ZEROIFNULL(WHS_OH_QTY) + ZEROIFNULL(STORE_OH_QTY) + ZEROIFNULL(CUST_DEMAND_FCST)

+ ZEROIFNULL(SHIP_UNSHIP) - ZEROIFNULL(CUST_POS_FCST)

else -- not the first week

SUM(ZEROIFNULL(WHS_OH_QTY) + ZEROIFNULL(STORE_OH_QTY) + ZEROIFNULL(CUST_DEMAND_FCST)

+ ZEROIFNULL(SHIP_UNSHIP) - ZEROIFNULL(CUST_POS_FCST))

OVER (ORDER BY PUBLISH_DATE,CUST_ITEM,CUST_WHS,CUST_WEEK ROWS UNBOUNDED PRECEDING)

end

as PROJ_INV

N/A

Re: using a calculation done in the previous row in this row's calculation

 I need this inventory to reset with each new item-location combination

This sounds like you could simply add a "PARTITION BY item, location" to the SUM.

Dieter

Re: using a calculation done in the previous row in this row's calculation

perfect.  thanks again Dieter.

jyo
N/A

Re: using a calculation done in the previous row in this row's calculation

I will be greatly appreciated if anyone could help on below problem.

I've data senerio like below and assume that there is no data prior to 2011and also previous amt values are derived by using olap preceding function so 2011 rows previous amount is zero and for 2012 aaa row previous amt is 20, now my question is with in the sql coding like preceding olap function is there is any function available to insert a row if a previous row is not found in current year, for ex: from the below senerio since 2011 abc is not found in 2012 year, a new row need to be added with the following values on each column ( 2012     abc  0 (curent amt) 10 (previous amt))

Year    company       amt          previous amt

2011     abc                  10                0

2011     aaa                   20               0

2012     aaa                   30               20

N/A

Re: using a calculation done in the previous row in this row's calculation

That's quite complicated.

A possible solution is a Cross Join between the list of years and the list of companies to create all combinations, followed by an Left Join to your query and finally the OLAP function the previous value.

Another solution will be based on creating Periods (current row's year, next row's year) and then EXPAND ON by year.

Is this for a view definition?

jyo
N/A

Re: using a calculation done in the previous row in this row's calculation

Thank you Dieter for prompt and quick response.

Can you please explain in detail or a sample code on
second option.
I am framing a query to create a view, this is very complicated qry which joins with huge data tables and at different levels, think that after going through all the process the query retrieved above given sample data. So i was looking for a solution just in the final step if row does not exist in current year when compared to previous year that should be added as a new for current, instead of going through huge table joins.
In the fist option you mentioned cross join years and company that means do i need to have a separate qry again joining with huge tables? Fyi when i checked the difference for two specific years for 2011 there are 30 companies which does not exist in 2012.
Thank you again for your valuable solutions.
N/A

Re: using a calculation done in the previous row in this row's calculation

Yes, you need to Cross Join years and companies, preferable not on the big table, better use the comanies table. But of course it's still a an extra step.

Regarding EXPAND ON, i wrote a similar query at 

http://forums.teradata.com/forum/database/query-on-csum

Agin, this is one or two extra steps on your large result set just to get those 30 missing rows.

jyo
N/A

Re: using a calculation done in the previous row in this row's calculation

Dieter,

In the above example, I did not specify all combinations of the company categories just not to confuse my situation with too many details.

In my case, looks like EXPAND ON is the best option then the cross join as the final query results are against multiple transactional tables, if I need to fetch distinct combinations of companies and subcategories need to go against multiple huge tables, which  will be pretty much same as what I have written to retrieve the final results.

And thanks for providing the query-on-csum forum, which is pretty much similar to my issue... but please excuse me, I really could not able to follow the code to frame customized Period parameters just for year  and I also searched for other forums in your website all of the examples are related to day or month. Could you please write the code specific to the year for the below example…

Assume Table Name is : CFORECAST

Year     company     amt     previous amt

2011       abc          10             0

2011       aaa          20             0

2012       aaa          30             20

2012       abc           0              10  --- {EXPAND ON  OLAP function Should GENERATE THIS ROW}

I truly appreciate your help...