怎么查出连续上班的的天数

Question

表tb_chuqin中有
empno        workdt           worktime      overtime
2130101      20130112           0               0
2130101      20130111           480             0
2130101      20130110           480             60
2130101      20130109           480             0
2130101      20130108           480             60
2130101      20130107           480             60
2130101      20130106           480             60
2130101      20130105           480             60
2130101      20130104           480             60
2130101      20130103           480             60
2130101      20130102           480             60
2130101      20130101           480             30
2130101      20121228           480             0
2130101      20121229           480             60
2130101      20121230           480             0
2130101      20121231           480             120
2130801      20121230           480             0表tb_kaoqin中有empno            workdt              timecd   
2130101          20130109             25
2130101          20130110             55当表tb_kaoqin中某天有数据或表tb_chuqin中 worktime+overtime=0时,那天就是有休息的
如果是当月,就算当月中最近休息那天到昨天是多少天,
如果是1号时就看上个月最近一次休息到月末是多少天,
select a.idno,max(a.workdt),to_date(to_char(sysdate - 1,'yyyymmdd'),'yyyymmdd') - to_date(max(a.workdt),'yyyymmdd') workday 
from tb_chuqin a,tb_kaoqin b
where  a.workdt between  to_char(trunc(sysdate-1,'mm'),'yyyymmdd') and to_char(sysdate-1,'yyyymmdd')
  and a.empno=b.empno(+)
  and a.workdt=b.workdt(+)
  and (nvl(a.worktime,0) + nvl(a.ottime,0) = 0 or substr(b.timecd,1,1) in ('1','2','3')  )
group by a.idno
上面的语句能查,但象今天查数据,如果某人没有休息从1号到15号都没有记录在tb_kaoqin中,而且在tb_chuqin中worktime+overtime都大于0,
那这部分人就查不出来了
要怎么写好呢

Answer 1

with tb_chuqin as
(select 2130101 empno,20130112 workdt ,0 worktime,0 overtime from dual
union all
select 2130101,20130111 , 480, 0  from dual union all
select 2130101,20130110 , 480, 60 from dual union all
select 2130101,20130109 , 480, 0  from dual union all
select 2130101,20130108 , 480, 60 from dual union all
select 2130101,20130107 , 480, 60 from dual union all
select 2130101,20130106 , 480, 60 from dual union all
select 2130101,20130105 , 480, 60 from dual union all
select 2130101,20130104 , 480, 60 from dual union all
select 2130101,20130103 , 480, 60 from dual union all
select 2130101,20130102 , 480, 60 from dual union all
select 2130101,20130101 , 480, 30 from dual union all
select 2130101,20121228 , 480, 0  from dual union all
select 2130101,20121229 , 480, 60 from dual union all
select 2130101,20121230 , 480, 0  from dual union all
select 2130101,20121231 , 480, 120 from dual union all
select 2130801,20121230 , 480, 0  from dual ),tb_kaoqin as(
select  2130101  empno,20130109  workdt,25 timecd from dual
union all
select   2130101,20130110,55 from dual)select empno,
       max(md) 最后休息时间,
       to_date(to_char(sysdate - 1, 'yyyymmdd'), 'yyyymmdd') -
       to_date(max(md), 'yyyymmdd') 连续工作时间
  from (select empno, max(workdt) as md
          from tb_chuqin
         where workdt between to_char(trunc(sysdate - 1, 'mm'), 'yyyymmdd') and
               to_char(sysdate - 1, 'yyyymmdd')
           and worktime + overtime = 0
         group by empno
        union all
        select empno, max(workdt) as md
          from tb_kaoqin
         where workdt between to_char(trunc(sysdate - 1, 'mm'), 'yyyymmdd') and
               to_char(sysdate - 1, 'yyyymmdd')
         group by empno)
 group by empno把两个表查询出的最后休息日期union起来再求最大就可以了

Answer 2

比方说一个人7号才来的,那么在tb_chuqin
从7号才有数据,而且一直在上班,worktime + overtime> 0 ,
而且没有请假,在tb_kaoqin 中也没有记录,
到昨天他连续上了7天,但这样写就没有统计到

Answer 3

试了下，这种情况确实有问题，想了下，把这种场景加进去，可以转化成对于没有休息的情况下求出第一天上班的日期，否则求出最后一次休息的日期。
测试的数据分为三种情况2130101在chuqin中有worktime+overtime=0,2130102为之前未考虑到的场景,2130103为kaoqin中有记录的with tb_chuqin as
(select 2130101 empno,20130116 workdt ,480 worktime,0 overtime from dual
union all
select 2130101,20130115 , 480, 60 from dual union all
select 2130101,20130114 , 480, 0  from dual union all
select 2130101,20130113 , 480, 60 from dual union all
select 2130101,20130112 , 480, 60 from dual union all
select 2130101,20130111 , 0, 0 from dual union all
select 2130101 ,20130110 , 480, 0  from dual union all
select 2130102 ,20130116 , 480, 0  from dual union all
select 2130102 ,20130115 , 480, 0  from dual union all
select 2130102 ,20130114 , 480, 0  from dual union all
select 2130103 ,20130116 , 480, 0  from dual union all
select 2130103 ,20130115 , 480, 0  from dual union all
select 2130103 ,20130114 , 480, 0  from dual union all
select 2130103 ,20130113 , 480, 0  from dual 
 ),
 tb_kaoqin as(
select  2130103  empno,20130114  workdt,25 timecd from dual)select empno,
       decode(is_work, 0, to_date(last_date, 'yyyymmdd')) 最后休息时间,
       to_date(to_char(sysdate - 1, 'yyyymmdd'), 'yyyymmdd') -
       decode(is_work,
              0,
              to_date(last_date, 'yyyymmdd'),
              to_date(last_date, 'yyyymmdd') - 1) 连续工作时间
  from (select empno,
               is_work,
               max(last_date) last_date,
               row_number() over(partition by empno order by is_work) as rn
          from (select empno,
                       sign(worktime + overtime) is_work,
                       decode(sign(worktime + overtime),
                              0,
                              max(workdt),
                              1,
                              min(workdt)) as last_date
                  from tb_chuqin
                 where workdt between
                       to_char(trunc(sysdate - 1, 'mm'), 'yyyymmdd') and
                       to_char(sysdate - 1, 'yyyymmdd')
                 group by empno, sign(worktime + overtime)
                union all
                select empno, 0, workdt from tb_kaoqin)
         group by empno, is_work)
 where rn = 1