Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.