MSSQL跨月连续上班统计，如何把跨月的连续天数统计在范围之内？

[天边一粒沙]

表结构
k_day(a0188,duty_date,out_address,out_time1,out_time2,out_time3)

语句
select A.A0188,A.RIQi,A.OUT_ADDRESS,ROW_NUMBER() OVER(PARTITION by A0188 ORDER BY riqi) as px, A.RIQi-ROW_NUMBER() OVER(PARTITION by A0188 ORDER BY riqi) as cf from
(select A0188,LEFT(CONVERT(VARCHAR(10),duty_date,112),8) as RiQi,OUT_ADDRESS from K_Day kd with (nolock) where DATEDIFF(DD,kd.duty_date,'2017-11-25')<=0 AND DATEDIFF(DD,kd.duty_date,'2017-12-24')>=0
AND (isnull(OUT_TIME1,0)+isnull(OUT_TIME2,0)+isnull(OUT_TIME3,0))>=0.1
AND OUT_ADDRESS IS NOT NULL
group by a0188,LEFT(CONVERT(VARCHAR(10),duty_date,112),8),OUT_ADDRESS
) A
group by A.A0188,A.RIQi,A.OUT_ADDRESS

执行如下结果


如图红色箭头，按照上述脚本，11月30 和12月的日期，日期减序号，得出的分组值，不在同一个分组里
即，跨月的出勤，连续上班，无法统计在范围之内，统计的结果，总是少把上个月份的天数少统计在范围内
求大神解答？能把跨月的连续上班，能够统计在一起。

[RINK_1]

你所谓的“连续”，是不是必须address也相同，否则即使前后连续两天都出勤，但是address不同，这仍认为是非连续的。如果是这种情况，试试下面的。

select A.A0188,
       A.OUT_ADDRESS,
       MIN(RIQi) as start_date,
       MAX(RIQi) as end_date,
       cf
from
(
select A.A0188,
       A.RIQi,
       A.OUT_ADDRESS,
       ROW_NUMBER() OVER(PARTITION by A0188 ORDER BY riqi) as px, 
       ROW_NUMBER() OVER(PARTITION by A0188 ORDER BY riqi)-ROW_NUMBER() OVER(PARTITION by A0188,OUT_ADDRESS ORDER BY riqi) as cf 
from
    (
     select A0188,
            LEFT(CONVERT(VARCHAR(10),duty_date,112),8) as RiQi,
            OUT_ADDRESS 
     from K_Day kd with (nolock)  
     where DATEDIFF(DD,kd.duty_date,'2017-11-25')<=0 
     AND DATEDIFF(DD,kd.duty_date,'2017-12-24')>=0 
     AND (isnull(OUT_TIME1,0)+isnull(OUT_TIME2,0)+isnull(OUT_TIME3,0))>=0.1
     AND OUT_ADDRESS IS NOT NULL
     group by a0188,LEFT(CONVERT(VARCHAR(10),duty_date,112),8),OUT_ADDRESS 
     ) A
 ) as A
group by A.A0188,A.OUT_ADDRESS,cf