All Forums Database
manib0907 61 posts Joined 04/15
09 Apr 2015
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

Cheers, Mani
Tags:
Fred 1096 posts Joined 08/04
09 Apr 2015

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?

manib0907 61 posts Joined 04/15
09 Apr 2015

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

Cheers,
Mani

manib0907 61 posts Joined 04/15
09 Apr 2015

Teradata 13

Cheers,
Mani

manib0907 61 posts Joined 04/15
09 Apr 2015

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
 

Cheers,
Mani

You must sign in to leave a comment.