Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-09-2015
06:23 AM

04-09-2015
06:23 AM

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

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-09-2015
09:31 AM

04-09-2015
09:31 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-09-2015
11:29 AM

04-09-2015
11:29 AM

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

Just a small correction in the example output

The last row should be 1 1 2

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-09-2015
11:29 AM

04-09-2015
11:29 AM

Teradata 13

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-09-2015
10:59 PM

04-09-2015
10:59 PM

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