Need help to calculate new column using the previous row new columns value

Analytics

Need help to calculate new column using the previous row new columns value

I have requirement to work on the below data and generate new column(Adj_start_date)  based on previous row value of new column.

Hope someone can help me with the SQL query to bring in the new column(Adj_start_date)  .

Couple of things:

  1. For each Cust_no and Brand_id combination i have multiple entries of start_date
  2. To start with the first value of Adj_start_date will be the Start_date

and from second occurrence it will be,

If current row of  Start_date  is less than previous row of Adj_start_date then Adj_start_date  will be previous row Adj_start_date  + Previous row no_of_days

If current row of  Start_date is greater than previous value of Adj_start_date then  Adj_start_date will be Start_date

If same cust_no and brand_id changes then Adj_start_date will be current row of Start_date.

  1. Cust_no and  Brand_id is partitioned by  and ordered by Start_date.

Tran_id

Cust_no

Brand_id

No_of_days

Start_date

Adj_start_date

511504171

51150417

40

90

4/29/2015

4/29/2015

211504176

51150417

40

60

06/28/2015

07/28/2015

111504179

51150417

50

90

05/02/2015

05/02/2015

571150410

51150417

48

45

07/10/2015

07/10/2015

514150413

51150417

78

90

06/28/2015

06/28/2015

511650414

51150417

78

70

07/10/2015

09/26/2015

51157041

51150417

78

60

9/01/2015

12/05/2015

12534078

51150417

78

40

02/01/2016

02/03/2016

12534079

51150417

78

90

10/03/2016

10/03/2016

12347890

51150417

86

5

12/20/2016

12/20/2016

12344890

51150417

86

10

12/30/2016

12/30/2016

12346890

51150417

86

10

1/1/2016

1/29/2016

22346890

51150417

86

10

1/10/2016

1/19/2016

 

514150413

1150417

78

90

06/28/2015

06/28/2015

511650414

1150417

78

70

07/10/2015

09/26/2015

51157041

1150417

78

60

9/01/2015

12/05/2015

12534078

1150417

78

40

02/01/2016

02/03/2016

12534079

1150417

78

90

10/03/2016

10/03/2016

12347890

1150417

86

5

12/20/2016

12/20/2016

12344890

1150417

86

10

12/30/2016

12/30/2016

12346890

1150417

86

10

1/1/2016

1/29/2016

22346890

1150417

86

10

1/10/2016

1/19/2016

 

Please let me is there any solution.I have to use BTEQ to achieve this.

  Thanks,

Reddy

6 REPLIES
Teradata Employee

Re: Need help to calculate new column using the previous row new columns value

Something like:

select Tran_id, Cust_no, Brand_id, Start_date,
max(Adj_start_date) over(partition by Cust_no, Brand_id order by Tran_id

       rows between 1 preceding and 1 preceding) as prev_start,
max(No_of_days) over(partition by Cust_no, Brand_id order by Tran_id

       rows between 1 preceding and 1 preceding) as prev_no_of_days,
case when Start_date < prev_start

       then prev_start + prev_no_of_days
else Start_date
end as Adj_start_date

from ...

When the Cust_no or Brand_id changes, the values of prev_start (and prev_no_of_days) will be null, so the case test for Start_date < prev_start (null) will be false, and Start_date will be the result.

Highlighted

Re: Need help to calculate new column using the previous row new columns value

Thanks Coleman.

When I ran below query it failed with prev_start not present.I think its not a defined column in FROM table.I think it will get same error even for Adj_start_date filed as we used max(Adj_start_date) which is new column which we are defining with this query.

 

Please suggest me further.

 

Thanks,

Reddy

 

Teradata Employee

Re: Need help to calculate new column using the previous row new columns value

Oh, well then just change max(Adj_start_date) over(...)  to  max(Start_date) over (...).

Re: Need help to calculate new column using the previous row new columns value

Thanks Coleman.

Query worked but results are not desired.

Its calcualting the new value of Adj_start_date based on the Prev_start but here really need to calculate it previous row value of Adj_start_date.

It breking the calucaltion if it has mutiple rows to satisfy below scenario

 

If current row of  Start_date  is less than previous row of Adj_start_date then Adj_start_date  will be previous row Adj_start_date  + Previous row no_of_days

 

514150413

51150417

78

90

06/28/2015

06/28/2015

511650414

51150417

78

70

07/10/2015

09/26/2015

51157041

51150417

78

60

9/01/2015

12/05/2015

12534078

51150417

78

40

02/01/2016

02/03/2016

12534079

51150417

78

90

10/03/2016

10/03/2016

12347890

51150417

86

5

12/20/2016

12/20/2016

 

Appreciated your time. Please suggest me further.

Thanks,

Reddy

Junior Contributor

Re: Need help to calculate new column using the previous row new columns value

Teradata Employee

Re: Need help to calculate new column using the previous row new columns value

I don't see how the logic you are stating goes with the sample data, but I have been thinking about it some more and haven't come up with an answer.  Maybe someone else will.