Oracle SQL查找日期范围中的差距

大家好，

我想获取日期范围之间的间隙，并需要它通过MM ID将间隙分开。

你能帮我解决下面的问题吗

输入:

ID | MM编号 | 从 | 到
12345 | 4444444 | 2015-01-03 | 2015-02
12346 | 4444444 | 2015-02-03 | 2015-03-02

<<缺少一条记录 (例如，从2015-03到2015-04-02的时间段内没有相同的MM编号

12347 | 4444444 | 2015-04-03 | 2015-05-01
12348 | 4444444 | 2015-05-02 | 2015-06-01
12349 | 4444444 | 2015-06-02 | 2015-07-01
12310 | 4444444 | 2015-07-02 | 2015-07-31

<<缺少一条记录 (例如: 从2015-08-01到2015-08-31的时间内没有相同的MM编号

12310 | 4444444 | 2015-09-01 | 2015-09-30



预期投放:
MM否 | 缺少开始日期 | 缺少到日期
4444444 | 2015-03 | 2015-04-02
4444444 | 2015-08-01 | 2015-08-31

我有一张下面数据的表格

创建表icr_tmp
(
身份证VARCHAR2(15)，
NMI VARCHAR2(50)，
发票 _ 开始 _ 日期日期，
INVOICE_END_DATE日期);

插入到icr_tmp中
值 (“12345” 、 “4444444” 、 “01/03/2015” 、 “02/02/2015”);

插入到icr_tmp中
值 (“12346” 、 “4444444” 、 “03/02/2015” 、 “02/03/2015”);

插入到icr_tmp中
值 (“12347” 、 “4444444” 、 “03/04/2015” 、 “01/05/2015”);

插入到icr_tmp中
值 (“12348” 、 “4444444” 、 “02/05/2015” 、 “01/06/2015”);

插入到icr_tmp中
值 (“12349” 、 “4444444” 、 “02/06/2015” 、 “01/07/2015”);

插入到icr_tmp中
值 (“12310” 、 “4444444” 、 “02/07/2015” 、 “31/07/2015”);

插入到icr_tmp中
值 (“12310” 、 “4444444” 、 “01/09/2015” 、 “30/09/2015”);

这里有一种方法可以做到这一点:

-生成您范围内所有日期的列表
-使用此方法查找表中开始/结束期间未涵盖的日期:

alter session set nls_date_format = 'dd/mm/yyyy';
create table icr_tmp (
ID VARCHAR2(15),
NMI VARCHAR2(50),
INVOICE_START_DATE DATE,
INVOICE_END_DATE DATE
);
insert into icr_tmp
values('12345','4444444','03/01/2015','02/02/2015');
insert into icr_tmp
values('12346','4444444','03/02/2015','02/03/2015');
insert into icr_tmp
values('12347','4444444','03/04/2015','01/05/2015');
insert into icr_tmp
values('12348','4444444','02/05/2015','01/06/2015');
insert into icr_tmp
values('12349','4444444','02/06/2015','01/07/2015');
insert into icr_tmp
values('12310','4444444','02/07/2015','31/07/2015');
insert into icr_tmp
values('12310','4444444','01/09/2015','30/09/2015');

with dates as (
  select date'2015-01-03'+level-1 dt from dual 
  connect by level <= (date'2015-08-31' - date'2015-01-03'+1)
), missing_dts as (
  select dt
  from   dates
  where  not exists (
    select null from icr_tmp
    where  dt between INVOICE_START_DATE and INVOICE_END_DATE
  )
)
  select * from missing_dts;

DT          
03/03/2015  
04/03/2015  
05/03/2015  
06/03/2015  
07/03/2015  
08/03/2015
...

要将此转换为缺少的开始/结束时段，请使用Tabibitosan方法。这通过以下方式将日期分为连续组:

-为每个分配一个row_number()
-从当前日期中减去这个rownum

这给予连续值相同的值。所以你现在需要做的就是按这个计算列分组，并返回最小和最大日期:

with dates as (
  select date'2015-01-03'+level-1 dt from dual 
  connect by level <= (date'2015-08-31' - date'2015-01-03'+1)
), missing_dts as (
  select dt, 
         dt - row_number() over (order by dt) grp
  from   dates
  where  not exists (
    select null from icr_tmp
    where  dt between INVOICE_START_DATE and INVOICE_END_DATE
  )
)
  select grp, min(dt), max(dt) from missing_dts
  group  by grp;

GRP         MIN(DT)     MAX(DT)     
02/03/2015  03/03/2015  02/04/2015  
30/06/2015  01/08/2015  31/08/2015 

有关Tabibitosan的更详细说明，请观看:

https://www.youtube.com/watch?v=yvimYixXo2Q