How to find the result of the previous row in Teradata

Database
Enthusiast

How to find the result of the previous row in Teradata

Input :

Key Gap

1 1

1 1

1 4

1 1

Output

Key Gap Overlap

1 1 1

1 1 1

1 4 2

1 1 1

i.e if the gap value is 1 then overlap should be set to the previous value and if it is not 1 then overlap should be previous value + 1. I tried using Rows unbounded preceding and not able to achieve the output.

How can this be achieved in teradata? Could anyone help?

Thanks

Mani

Tags (1)
4 REPLIES
Teradata Employee

Re: How to find the result of the previous row in Teradata

How do you get the rows into the order in your example? What are you using for PARTITION BY / ORDER BY in the OVER clause?

And which database release?

Enthusiast

Re: How to find the result of the previous row in Teradata

Yes, I am using both partition by and order by clause. I have date fields based on which I am ordering and partitioning on the key column.
Just a small correction in the example output
The last row should be 1 1 2
Enthusiast

Re: How to find the result of the previous row in Teradata

Teradata 13
Enthusiast

Re: How to find the result of the previous row in Teradata

Hi All,

I have found the solution of the above problem.

I loaded a tabl with the below values

Key    Gap     Overlap

1        1            1

1        1            0

1        4            1

1        1            0

i.e for the frst record it overlap value is 1 and if the gap value is 1 then it will be 0 and if greater than 1 then 1. Lets say that this is in table A

in the next table

I ran the following query

Select

Key, Gap, Sum(Overlap) over (partition by key rows unbounded preceding) as crctd_overlap

The result will be the desired output

Thanks,

Mani