8-46 YYYYMM形式の補完(個別の最小から最大)

SQLパズル

yyyymm3
Code  yyyymm
----  ------
AAAA  200811
AAAA  200902
BBBB  200801
BBBB  200805
CCCC  200810
DDDD  200803
DDDD  200804
DDDD  200805

Codeごとに、
yyyymmを、同一Codeでの最小から最大までを補完しつつ出力する。
existFlagで存在の有無を表示する。

出力結果
Code  yyyymm  existFlag
----  ------  ---------
AAAA  200811  1
AAAA  200812  0
AAAA  200901  0
AAAA  200902  1
BBBB  200801  1
BBBB  200802  0
BBBB  200803  0
BBBB  200804  0
BBBB  200805  1
CCCC  200810  1
DDDD  200803  1
DDDD  200804  1
DDDD  200805  1

データ作成スクリプト

create table yyyymm3(Code,yyyymm) as
select 'AAAA',200811 from dual union all
select 'AAAA',200902 from dual union all
select 'BBBB',200801 from dual union all
select 'BBBB',200805 from dual union all
select 'CCCC',200810 from dual union all
select 'DDDD',200803 from dual union all
select 'DDDD',200804 from dual union all
select 'DDDD',200805 from dual;

SQL

col yyyymm for a10

select Code,to_char(add_months(to_date(to_char(yyyymm),'yyyymm'),b.Counter-1),'yyyymm') as yyyymm,
decode(b.Counter,1,1,0) as existFlag
from (select Code,yyyymm,
      Lead(yyyymm) over(partition by Code order by yyyymm) as next
        from yyyymm3) a,(select RowNum as Counter from dict) b
where case when next is null then 1
      else months_between(to_date(to_char(a.next)  ,'yyyymm')
                         ,to_date(to_char(a.yyyymm),'yyyymm')) end >= b.Counter
order by Code,yyyymm;

解説

months_between関数と、add_months関数を使って年またぎに対応しています。

---