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

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
Senior Apprentice

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
Teradata Employee

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

Teradata Employee

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!

 

select failed. 5628: column Old_OH not found in qry

Teradata Employee

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,

 

Thanks for the reply.

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:

1. Column not found

2. Ordered Analytical functions cannot be nested

 

Appreciate your insights and help!

 

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_DEVNT_TSITEMSTR_IFULFILLMENTDMND_QBOHROW_NUMNEW_OHOLD_OHNEW_OH1OLD_OH1
11/24/201618:57:1351,150,4174STORE1211212
11/24/201617:48:1251,150,4174SPU1221211
11/24/201616:23:1551,150,4175SPU1312323
11/24/201620:12:4451,150,41748STORE1312323
11/24/201620:18:5451,150,41769STORE3310303
11/24/201618:27:1451,150,41778STORE1312323
11/24/201622:59:3551,150,41778STORE1322322
11/24/201617:24:1151,150,41778SPU1332322
11/24/201619:20:4651,150,41780STORE1110101
Senior Apprentice

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 :)