日期中的 SQL 间隔

Question

我试图根据表格看起来像这样的状态代码来查找表格中的空白。

状态表:

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

状态模型表:

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

我正在使用的查询如下:

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

但是通过上表示例，我得到以下输出:

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

无论如何要重组我的查询，以便不显示 1 和 3 之间的差距，因为 1 和 2 之间没有差距？
我正在使用 MS sql server 2008
谢谢

Answer 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)

为了使其尽可能有效，还要在 (StateID, EfftiveDate) 上添加一个索引。