Database

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

01-22-2009
11:24 AM

01-22-2009
11:24 AM

I am having trouble with the scenario below.

Here is an example of what would be a select * against the table I am looking at:

loc eff_dt exp_dt

X 9/23/2007 9/12/2008

X 12/24/2008 6/6/2079

Y 1/1/2007 12/31/2008

Y 1/1/2009 6/6/2079

As you can see loc Y has a consistent date range (or no gap) from its first exp_dt (on the third row) to its second eff_dt (on the fourth row).

However, there is a gap in the date range for loc X between its first exp_dt (on the first row) to its second eff_dt (on the second row).

I am trying to identify all the locs with gaps. So in the example above if I queried correctly I would only retrieve the loc X rows.

I was told using a CSUM function may work, but I have not figured it out.

Thanks.

Here is an example of what would be a select * against the table I am looking at:

loc eff_dt exp_dt

X 9/23/2007 9/12/2008

X 12/24/2008 6/6/2079

Y 1/1/2007 12/31/2008

Y 1/1/2009 6/6/2079

As you can see loc Y has a consistent date range (or no gap) from its first exp_dt (on the third row) to its second eff_dt (on the fourth row).

However, there is a gap in the date range for loc X between its first exp_dt (on the first row) to its second eff_dt (on the second row).

I am trying to identify all the locs with gaps. So in the example above if I queried correctly I would only retrieve the loc X rows.

I was told using a CSUM function may work, but I have not figured it out.

Thanks.

6 REPLIES

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

01-22-2009
03:55 PM

01-22-2009
03:55 PM

Assuming you will only have two rows per loc, try this:

SEL * FROM TEST3

WHERE LOC IN

(

SEL LOC FROM

(

SEL DT1.LOC AS LOC

, DT2.EFF_DT - DT1.EXP_DT AS GAP

FROM

(SEL * FROM test3

QUALIFY ROW_NUMBER() OVER (PARTITION BY LOC ORDER BY EXP_DT) = 1

) DT1

,

(SEL * FROM test3

QUALIFY ROW_NUMBER() OVER (PARTITION BY LOC ORDER BY EXP_DT) = 2

) DT2

WHERE DT1.LOC = DT2.LOC

) DT3

WHERE DT3.GAP > 1

)

SEL * FROM TEST3

WHERE LOC IN

(

SEL LOC FROM

(

SEL DT1.LOC AS LOC

, DT2.EFF_DT - DT1.EXP_DT AS GAP

FROM

(SEL * FROM test3

QUALIFY ROW_NUMBER() OVER (PARTITION BY LOC ORDER BY EXP_DT) = 1

) DT1

,

(SEL * FROM test3

QUALIFY ROW_NUMBER() OVER (PARTITION BY LOC ORDER BY EXP_DT) = 2

) DT2

WHERE DT1.LOC = DT2.LOC

) DT3

WHERE DT3.GAP > 1

)

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

01-23-2009
01:43 PM

01-23-2009
01:43 PM

SELECT DISTINCT loc

FROM

(

SELECT

loc,

eff_dt -

MIN(exp_dt) OVER (PARTITION BY loc ORDER BY exp_dt

ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS diff

FROM tab

QUALIFY diff > 1

) dt

Diete

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

01-30-2009
12:52 PM

01-30-2009
12:52 PM

Thanks, this helps me get the row after the gap. But how would I get the row before the gap also?

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

01-30-2009
12:53 PM

01-30-2009
12:53 PM

By the way this is in response to this example:

SELECT DISTINCT loc

FROM

(

SELECT

loc,

eff_dt -

MIN(exp_dt) OVER (PARTITION BY loc ORDER BY exp_dt

ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS diff

FROM tab

QUALIFY diff > 1

) dt

Thanks.

SELECT DISTINCT loc

FROM

(

SELECT

loc,

eff_dt -

MIN(exp_dt) OVER (PARTITION BY loc ORDER BY exp_dt

ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS diff

FROM tab

QUALIFY diff > 1

) dt

Thanks.

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

01-30-2009
04:34 PM

01-30-2009
04:34 PM

Just add another calculation:

SELECT

tab.*,

eff_dt -

MIN(exp_dt) OVER (PARTITION BY loc ORDER BY exp_dt

ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS diff,

exp_dt -

MAX(eff_dt) OVER (PARTITION BY loc ORDER BY exp_dt

ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS diff2

FROM tab

QUALIFY diff > 1 OR diff2 < -1

Dieter

SELECT

tab.*,

eff_dt -

MIN(exp_dt) OVER (PARTITION BY loc ORDER BY exp_dt

ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS diff,

exp_dt -

MAX(eff_dt) OVER (PARTITION BY loc ORDER BY exp_dt

ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS diff2

FROM tab

QUALIFY diff > 1 OR diff2 < -1

Dieter

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

02-02-2009
12:06 PM

02-02-2009
12:06 PM

Awesome. It worked. Thank you very much.

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.