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

天边一粒沙 2017-11-17 08:36:38

表结构
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月的日期,日期减序号,得出的分组值,不在同一个分组里
即,跨月的出勤,连续上班,无法统计在范围之内,统计的结果,总是少把上个月份的天数少统计在范围内
求大神解答?能把跨月的连续上班,能够统计在一起。
...全文
228 1 点赞 打赏 收藏 举报
写回复
1 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
RINK_1 2017-11-17
你所谓的“连续”,是不是必须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
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
加入

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2017-11-17 08:36
社区公告
暂无公告