Database
Fan

## 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.

6 REPLIES

## 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
)
Junior Contributor

## 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
Fan

## 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?

Fan

## 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.
Junior Contributor

## Re: Determining Gaps in Date Ranges

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
Fan

## Re: Determining Gaps in Date Ranges

Awesome. It worked. Thank you very much.