10-350 担当した期間をまとめる

SQLパズル

担当テーブル
ID  Name  StaD
--  ----  ----------
 1  AAAA  2006-01-01
 2  AAAA  2006-02-03
 3  BBBB  2006-03-21
 4  AAAA  2006-04-13
 5  BBBB  2007-01-01
 6  BBBB  2007-09-01

担当した期間を下記のようにまとめる

出力結果
ID  Name  StaD        EndD
--  ----  ----------  ----------
 1  AAAA  2006-01-01  2006-03-20
 3  BBBB  2006-03-21  2006-04-12
 4  AAAA  2006-04-13  2006-12-31
 5  BBBB  2007-01-01  null

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table 担当テーブル(ID primary key,Name,StaD) as
select 1,'AAAA',date '2006-01-01' from dual union
select 2,'AAAA',date '2006-02-03' from dual union
select 3,'BBBB',date '2006-03-21' from dual union
select 4,'AAAA',date '2006-04-13' from dual union
select 5,'BBBB',date '2007-01-01' from dual union
select 6,'BBBB',date '2007-09-01' from dual;

SQL

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd';

--■■■旅人算メソッドを使わない方法■■■
with tmp as(
select ID,Name,StaD,
case when Name = Lag(Name) over(order by StaD)
     then 0 else 1 end as IsStart
  from 担当テーブル)
select ID,Name,StaD,
Lead(StaD) over(order by StaD) - 1 as EndD
  from tmp
 where IsStart = 1
order by StaD;

--■■■旅人算メソッドを使う方法■■■
with tmp as(
select ID,Name,StaD,
 Row_Number() over(                  order by StaD)
-Row_Number() over(partition by Name order by StaD) as Dis
  from 担当テーブル)
select min(ID),Name,min(StaD),
Lead(min(StaD)) over(order by min(StaD)) -1 as EndD
  from tmp
group by Name,Dis
order by min(ID);

解説

最初に思いついたのは、旅人算メソッドを使う方法でしたが、
旅人算メソッドを使わないほうが優れてますね。

旅人算メソッドは、銀の弾丸ではないのです。