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

01-22-2009
11:24 AM

01-22-2009
11:24 AM

Determining Gaps in Date Ranges

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

Re: Determining Gaps in Date Ranges

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

Re: Determining Gaps in Date Ranges

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

Re: Determining Gaps in Date Ranges

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

Re: Determining Gaps in Date Ranges

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

Re: Determining Gaps in Date Ranges

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

Re: Determining Gaps in Date Ranges

Awesome. It worked. Thank you very much.