SQL 连续七天未上班

人事系统经常需要统计考勤信息，如连续七天未上班、连续七天上班等，已知有刷卡记录表如下；

CREATE TABLE [dbo].[K07](
    [A0188] [int] NOT NULL,--人员id
    [K0700] [datetime] NOT NULL,--刷卡日期（只有日期）
    [K0701] [datetime] NOT NULL,--刷卡时间（时分秒）
}

连续七天未上班sql代码：

WITH    ks--按人分组查询刷卡日期和序号
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY k.a0188 ORDER BY k.a0188, k.k0700 ) sn ,
                        k.a0188 ,
                        k.k0700
               FROM     k07 k
               WHERE    k0700 BETWEEN '20130101' AND '20131231'
               GROUP BY k.a0188 ,
                        k0700
             )
             --两条序号相邻刷卡日期差值大于7
    SELECT  a.a0188 ,
            a.k0700 + 1 ksrq ,
            b.k0700 - 1 jsrq ,
            DATEDIFF(d, a.k0700, b.k0700) - 1 ts
    FROM    ks a ,
            ks b
    WHERE   a.a0188 = b.a0188
            AND b.sn - a.sn = 1
            AND DATEDIFF(d, a.k0700, b.k0700) > 7

连续七天上班sql代码：

WITH    ks
          --按人分组查询刷卡日期和序号
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY k.a0188 ORDER BY k.a0188, k.k0700 ) sn ,
                        k.a0188 ,
                        k.k0700
               FROM     k07 k
               WHERE    k0700 BETWEEN '20130101' AND '20131231'
               GROUP BY k.a0188 ,
                        k0700
             ),
        tj
          --计算刷卡日期和其序号差值，差值相同的刷卡日期连续
          AS ( SELECT   a0188 ,
                        k0700 ,
                        k0700 - sn diff ,
                        sn
               FROM     ks
             )
    SELECT  a0188 ,
            --diff ,
            MIN(k0700) ksrq ,
            MAX(k0700) jsrq ,
            COUNT(1) ts
    FROM    tj
    GROUP BY a0188 ,
            diff
    HAVING  COUNT(1) > 7
    ORDER BY a0188 ,
            diff