SQL gaps in dates

问题:

I am trying to find gaps in the a table based on a state code the tables look like this.

StateTable:

StateID (PK) | Code
--------------------
1            | AK
2            | AL
3            | AR

StateModel Table:

StateModelID | StateID | EfftiveDate            | ExpirationDate
-------------------------------------------------------------------------
1            |  1      | 2012-06-28 00:00:00.000| 2012-08-02 23:59:59.000
2            |  1      | 2012-08-03 00:00:00.000| 2050-12-31 23:59:59.000
3            |  1      | 2055-01-01 00:00:00.000| 2075-12-31 23:59:59.000

The query I am using is the following:

Declare @gapMessage varchar(250)
SET @gapMessage = ''

select
@gapMessage = @gapMessage + 
  (Select StateTable.Code FROM StateTable where t1.StateID = StateTable.StateID) 
  + ' Row ' +CAST(t1.StateModelID as varchar(6))+' has a gap with '+
  CAST(t2.StateModelID as varchar(6))+ CHAR(10)
   from StateModel t1
   inner join StateModel t2
    on
       t1.StateID = t2.StateID
       and DATEADD(ss, 1,t1.ExpirationDate) < t2.EffectiveDate
       and t1.EffectiveDate < t2.EffectiveDate

 if(@gapMessage != '')
 begin         
Print 'States with a gap problem'
PRINT @gapMessage
 end
 else
 begin
PRINT 'No States with a gap problem'
 end

But with the above table example I get the following output:

States with a gap problem
AK Row 1 has a gap with 3
AK Row 2 has a gap with 3

Is there anyway to restructure my query so that the gap between 1 and 3 does not display because there is not a gap between 1 and 2? I am using MS sql server 2008 Thanks

回答1:

WITH
  sequenced AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY StateID ORDER BY EfftiveDate) AS SequenceID,
    *
  FROM
    StateModel
)
SELECT
  *
FROM
  sequenced    AS a
INNER JOIN
  sequenced    AS b
    ON  a.StateID    = b.StateID
    AND a.SequenceID = b.SequenceID - 1
WHERE
  a.ExpirationDate < DATEADD(second, -1, b.EfftiveDate)

To make this as effective as possible, also add an index on (StateID, EfftiveDate)

回答2:

I wanted to just give credit to MatBailie, but don't have the points to do it yet, so I thought I would help out anyone else looking for a similar solution that may want to take it a step further like I needed to. I have changed my application of his code (which involves member enrollment) to the same language as the example here.

In my case, I needed these things:

1. I have two similar tables that I need to develop into one total table. In this example, let's make the tables like this: SomeStates + OtherStates = UpdatedTable. These are UNIONED in the AS clause.
2. I didn't want to remove any rows due to gaps, but I wanted to flag them on the StateID level. This is added as an additional column 'StateID_GapFlag'.

3. I also wanted to add a column to hold the oldest or MIN(EffectiveDate). This would be used in later calculations of SUM(period) to get a total duration, excluding gaps. This is the column 'MIN_EffectiveDate'.

   ;WITH sequenced     
   ( SequenceID
   ,EffectiveDate
   ,ExpirationDate)
   AS
       (select 
       ROW_NUMBER() OVER (PARTITION BY StateID ORDER by EffectiveDate) as SequenceID,
       * from  (select EffectiveDate, ExpirationDate from SomeStates
                   UNION ALL
               (select EffectiveDate, ExpirationDate from OtherStates)
               ) StateModel
       where 
           EffectiveDate > 'filter'
       )
   Select DISTINCT 
           IJ1.[MIN_EffectiveDate]
           ,coalesce(IJ2.GapFlag,'') as [MemberEnrollmentGapFlag]
           ,EffectiveDate
           ,ExpirationDate
   into UpdatedTable 
   from sequenced seq
           inner join 
               (select StateID, min(EffectiveDate) as 'MIN_EffectiveDate'
                   from sequenced 
                   group by StateID
               ) IJ1
           on seq.member# = IJ1.member
           left join
               (select a.member#, 'GAP' as 'StateID_GapFlag'
                   from sequenced a
                   inner join 
                       sequenced b
                           on a.StateID = b.StateID
                           and a.SequenceID = (b.sequenceID - 1)
                       where a.ExpirationDate < DATEADD(day, -1, b.EffectiveDate)
               ) LJ2
           on seq.StateID = LJ2.StateID
   

回答3:

You could use ROW_NUMBER to provide an ordering of stateModel's for each state, then check that the second difference for consecutive rows doesn't exceed 1. Something like:

;WITH Models (StateModelID, StateID, Effective, Expiration, RowOrder) AS (
    SELECT StateModelID, StateID, EffectiveDate, ExpirationDate,
           ROW_NUMBER() OVER (PARTITION BY StateID, ORDER BY EffectiveDate)
    FROM StateModel
)
SELECT F.StateModelId, S.StateModelId
FROM Models F
CROSS APPLY (
    SELECT M.StateModelId
    FROM Models M
    WHERE M.RowOrder = F.RowOrder + 1
      AND M.StateId = F.StateId
      AND DATEDIFF(SECOND, F.Expiration, M.Effective) > 1
) S

This will get you the state model IDs of the rows with gaps, which you can format how you wish.