How to find Date Gap Issue in Type-2 data?

How to find Multiple Open records:

Multiple active records in the table is not good for EDW. Users will be very much affected by these. So, it is very much important for testers to test properly and carefully for multiple open records. For any change in the table always validate multiple open records. Below is the sample query to test them:

SELECT ACCT_NBR, COUNT(1)

FROM DW_MART.ACCT_PAYOFF_QUOTE_DIM

WHERE CLOSE_DT_ID = 99991231

GROUP BY ACCT_NBR

HAVING COUNT(1)>1;

How to find Date Gaps or Date Overlapping:

Date gaps and data overlapping means the records is not closing properly in the warehouse.

ACCT_NBR	OPEN_DT_ID	CLOSE_DT_ID
864175344	19000101	20130115
864175344	20130120	20130130	The record in the previous row is expiring at 2013-01-15, but in the next row it is opening at 2013-01-20, that means there is a date gap of 5 days which is not correct.
864175344	20130130	99991231	The record in the previous row is expiring at 2013-01-15, but in the next row it is opening at 2013-01-30 again, that means there is a date overlapping on the same day,  which is not correct.

The below is sample query, based on the natural keys, and table name please change the query appropriately.

SELECT

    *

FROM

    (SELECT

        ACCT_NBR,

        OPEN_DT_ID,

        TO_DATE (CLOSE_DT_ID, 'YYYYMMDD') - TO_DATE(LEAD(OPEN_DT_ID,1,CLOSE_DT_ID)

            OVER (PARTITION BY ACCT_NBR ORDER BY CLOSE_DT_ID ASC), 'YYYYMMDD') BOUNDARY_CHECK

    FROM

        DW_MART.ACCT_PAYOFF_QUOTE_DIM)

WHERE

    BOUNDARY_CHECK NOT IN (0, -1, 2917854);