Database
Enthusiast

## Need help to calculate new columns using the previous row in this row's calculation

Hi All,

I have requirement to work on the below data and generate two new columns based on previous row values. Hope someone can help me with the SQL query to bring in the new columns.

Couple of things:

1. For each ITEM and STR_I combination i have multiple entries of DMND_Q by FULFILMENT.
2. To start with the first value of Old OH will be the BOH and from second occurrence it will the previous value of New OH
3. New OH calculation is New OH = Old OH - DMND_Q
4. Both Old and New OH are partition by ITEM and  STR_I ad ordered by EVNT_TS.
 ORD_D EVNT_TS ITEM STR_I FULFILLMENT DMND_Q BOH Old OH New OH 11/24/2016 18:57:13 51150417 4 STORE 1 2 2 1 11/24/2016 17:48:12 51150417 4 SPU 1 2 1 0 11/24/2016 16:23:15 51150417 5 SPU 1 3 3 2 11/24/2016 20:12:44 51150417 48 STORE 1 3 3 2 11/24/2016 18:27:14 51150417 78 STORE 1 3 3 2 11/24/2016 22:59:35 51150417 78 STORE 1 3 2 1 11/24/2016 17:24:11 51150417 78 SPU 1 3 1 0

Please let me know if you have any questions.

Thanks,

Swetha

Accepted Solutions
Junior Contributor

## Re: Need help to calculate new columns using the previous row in this row's calculation

Looks like you simply need to substract a Cumulative Sums of the demand queue (DMND_Q) from the items on hand (BOH).

If BOH is actually the same value for all rows of a (ITEM, STR_I) combination it's

```BOH + DMND_Q
- Sum(DMND_Q)
Over (PARTITION BY ITEM, STR_I
ORDER BY ORD_D, EVNT_TS
ROWS Unbounded Preceding)
AS Old_OH,
BOH
- Sum(DMND_Q)
Over (PARTITION BY ITEM, STR_I
ORDER BY ORD_D, EVNT_TS
ROWS Unbounded Preceding)
AS New_OH```

Otherwise replace BOH with

```First_Value(BOH)
Over (PARTITION BY ITEM, STR_I
ORDER BY ORD_D, EVNT_TS)```

1 ACCEPTED SOLUTION
7 REPLIES

## Re: Need help to calculate new columns using the previous row in this row's calculation

Try something like:

select ORD_D, EVNT_TS, ITEM, STR_I, FULFILLMENT, DMND_Q, BOH,
(CASE When MIN(New_OH) OVER (Partition by ITEM, STR_I Order by ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) is NULL Then BOH
Else MIN(New_OH) OVER (Partition by ITEM, STR_I Order by ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
END) as Old_OH,
(CASE When MIN(New_OH) OVER (Partition by ITEM, STR_I Order by ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) is NULL Then BOH
Else MIN(New_OH) OVER (Partition by ITEM, STR_I Order by ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
END) as Old_OH,
(Old_OH - DMND_Q) as New_OH
Order by 1,2,3,4

## Re: Need help to calculate new columns using the previous row in this row's calculation

Sorry, too many lines in that one!

select ORD_D, EVNT_TS, ITEM, STR_I, FULFILLMENT, DMND_Q, BOH,
(CASE When MIN(New_OH) OVER (Partition by ITEM, STR_I Order by ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) is NULL Then BOH
Else MIN(New_OH) OVER (Partition by ITEM, STR_I Order by ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
END) as Old_OH,
(Old_OH - DMND_Q) as New_OH
Order by 1,2,3,4

Enthusiast

## Re: Need help to calculate new columns using the previous row in this row's calculation

Thanks for responding. I tried thatearlier too and ran with the below error. Am I missing something here!

## Re: Need help to calculate new columns using the previous row in this row's calculation

Then I guess you have two options.  One is:

select ORD_D, EVNT_TS, ITEM, STR_I, FULFILLMENT, DMND_Q, BOH,
(CASE When MIN(New_OH) OVER (Partition by ITEM, STR_I Order by ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) is NULL Then BOH
Else MIN(New_OH) OVER (Partition by ITEM, STR_I Order by ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
END) as Old_OH,
/* (Old_OH - DMND_Q) as New_OH */
(CASE When MIN(New_OH) OVER (Partition by ITEM, STR_I Order by ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) is NULL Then BOH
Else MIN(New_OH) OVER (Partition by ITEM, STR_I Order by ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
END) - DMND_Q as New_OH,
Order by 1,2,3,4

But this might be easier to read:

select T.ORD_D, T.EVNT_TS, T.ITEM, T.STR_I, T.FULFILLMENT, T.DMND_Q, T.BOH,
(CASE When T.Old_OH is NULL Then T.BOH Else T.Old_OH END) as Old_OH,
(T.Old_OH - T.DMND_Q) as New_OH
from
( select ORD_D, EVNT_TS, ITEM, STR_I, FULFILLMENT, DMND_Q, BOH,
MIN(New_OH) OVER (Partition by ITEM, STR_I Order by ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as Old_OH
) T
Order by 1,2,3,4

I would try both forms and see if one is faster than the other.  If they are pretty close then I would prefer the one that is easier for the next person who has to read this.

Enthusiast

## Re: Need help to calculate new columns using the previous row in this row's calculation

Hi Coleman,

The problem with the below query is "Column New_OH not found"  because at the first pass this coulmn will not be found.

select T.ORD_D, T.EVNT_TS, T.ITEM, T.STR_I, T.FULFILLMENT, T.DMND_Q, T.BOH,
(CASE When T.Old_OH is NULL Then T.BOH Else T.Old_OH END) as Old_OH,
(T.Old_OH - T.DMND_Q) as New_OH
from
( select ORD_D, EVNT_TS, ITEM, STR_I, FULFILLMENT, DMND_Q, BOH,
MIN(New_OH) OVER (Partition by ITEM, STR_I Order by ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as Old_OH
) T
Order by 1,2,3,4

I tried the below query of my own and still post the 2nd iteration the query doesnt work. Here NEW_OH1 and OLD_OH1 will be my latest columns.

The 2 common errors i face with different queries i tried are:

2. Ordered Analytical functions cannot be nested

SyntaxEditor Code Snippet

```SELECT ORD_D, EVNT_TS, ITEM, STR_I, FULFILLMENT, DMND_Q, BOH,ROW_NUMBER() OVER (PARTITION BY ITEM, STR_I  ORDER BY evnt_ts ) AS ROW_NUM,
(BOH-DMND_Q) AS NEW_OH, BOH AS OLD_OH,

(CASE WHEN ROW_NUM =1 THEN NEW_OH
ELSE  ((MIN (OLD_OH)  OVER (PARTITION BY ITEM, STR_I  ORDER BY ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING))-DMND_Q) END) AS NEW_OH1,
( CASE WHEN ROW_NUM = 1 THEN OLD_OH
ELSE  (MIN (NEW_OH)  OVER (PARTITION BY ITEM, STR_I  ORDER BY ORD_D, EVNT_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) END )  AS OLD_OH1
FROM
TABLE1
GROUP BY 1,2,3,4,5,6,7```
 ORD_D EVNT_TS ITEM STR_I FULFILLMENT DMND_Q BOH ROW_NUM NEW_OH OLD_OH NEW_OH1 OLD_OH1 11/24/2016 18:57:13 51,150,417 4 STORE 1 2 1 1 2 1 2 11/24/2016 17:48:12 51,150,417 4 SPU 1 2 2 1 2 1 1 11/24/2016 16:23:15 51,150,417 5 SPU 1 3 1 2 3 2 3 11/24/2016 20:12:44 51,150,417 48 STORE 1 3 1 2 3 2 3 11/24/2016 20:18:54 51,150,417 69 STORE 3 3 1 0 3 0 3 11/24/2016 18:27:14 51,150,417 78 STORE 1 3 1 2 3 2 3 11/24/2016 22:59:35 51,150,417 78 STORE 1 3 2 2 3 2 2 11/24/2016 17:24:11 51,150,417 78 SPU 1 3 3 2 3 2 2 11/24/2016 19:20:46 51,150,417 80 STORE 1 1 1 0 1 0 1
Junior Contributor

## Re: Need help to calculate new columns using the previous row in this row's calculation

Looks like you simply need to substract a Cumulative Sums of the demand queue (DMND_Q) from the items on hand (BOH).

If BOH is actually the same value for all rows of a (ITEM, STR_I) combination it's

```BOH + DMND_Q
- Sum(DMND_Q)
Over (PARTITION BY ITEM, STR_I
ORDER BY ORD_D, EVNT_TS
ROWS Unbounded Preceding)
AS Old_OH,
BOH
- Sum(DMND_Q)
Over (PARTITION BY ITEM, STR_I
ORDER BY ORD_D, EVNT_TS
ROWS Unbounded Preceding)
AS New_OH```

Otherwise replace BOH with

```First_Value(BOH)
Over (PARTITION BY ITEM, STR_I
ORDER BY ORD_D, EVNT_TS)```

Enthusiast

## Re: Need help to calculate new columns using the previous row in this row's calculation

PERFECT!! it worked.. thanks both for your time :)