8-45 YYYYMM形式の補完(全体の最小から最大)

SQLパズル

yyyymm2テーブル
Code  yyyymm
----  ------
AAAA  200811
AAAA  200902
BBBB  200903
CCCC  200902
DDDD  200903

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

出力結果
Code  yyyymm  existFlag
----  ------  ---------
AAAA  200811  1
AAAA  200812  0
AAAA  200901  0
AAAA  200902  1
AAAA  200903  0
BBBB  200811  0
BBBB  200812  0
BBBB  200901  0
BBBB  200902  0
BBBB  200903  1
CCCC  200811  0
CCCC  200812  0
CCCC  200901  0
CCCC  200902  1
CCCC  200903  0
DDDD  200811  0
DDDD  200812  0
DDDD  200901  0
DDDD  200902  0
DDDD  200903  1

データ作成スクリプト

create table yyyymm2(Code,yyyymm) as
select 'AAAA',200811 from dual union all
select 'AAAA',200902 from dual union all
select 'BBBB',200903 from dual union all
select 'CCCC',200902 from dual union all
select 'DDDD',200903 from dual;

SQL

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select c.Code,a.yyyymm,nvl2(c.yyyymm,1,0) as existFlag
from (select to_number(to_char(add_months(to_date(a.yyyymm,'yyyymm'),b.Counter-1),'yyyymm')) as yyyymm
      from (select yyyymm,
            case when next is null then 1
                 else months_between(to_date(substr(next  ,1,4) || substr(next  ,5),'yyyymm'),
                                     to_date(substr(yyyymm,1,4) || substr(yyyymm,5),'yyyymm'))
                 end as JoinCount
            from (select yyyymm,Lead(yyyymm) over(order by yyyymm) as next
                  from (select distinct yyyymm from yyyymm2))) a,(select RowNum as Counter from dict) b
      where a.JoinCount >= b.Counter) a
      Left Join yyyymm2 c
      partition by (c.Code)
      on (a.yyyymm = c.yyyymm)
order by c.Code,a.yyyymm;

--■■■PL/SQLを使う方法■■■
declare
    firstEachCode boolean := true;
    WKYYYYmm pls_Integer;
begin
    for rec in (select Code,yyyymm,min(yyyymm) over() as minYM,max(yyyymm) over() as maxYM,
                Lead(yyyymm) over(partition by Code order by yyyymm) as next
                  from yyyymm2
                order by Code,yyyymm) Loop

        if firstEachCode then
            firstEachCode := false;
            WKYYYYmm := rec.minYM;

            while (WKYYYYmm < rec.yyyymm) Loop
                DBMS_Output.Put_Line(rec.Code || '■' || WKYYYYmm || '■0');
                WKYYYYmm := case mod(WKYYYYmm,100) when 12 then WKYYYYmm -12+101 else WKYYYYmm+1 end;
            end loop;
        end if;

        DBMS_Output.Put_Line(rec.Code || '■' || rec.yyyymm || '■1');

        WKYYYYmm := rec.yyyymm;
        WKYYYYmm := case mod(WKYYYYmm,100) when 12 then WKYYYYmm -12+101 else WKYYYYmm+1 end;

        while (WKYYYYmm < rec.next) Loop
            DBMS_Output.Put_Line(rec.Code || '■' || WKYYYYmm || '■0');
            WKYYYYmm := case mod(WKYYYYmm,100) when 12 then WKYYYYmm -12+101 else WKYYYYmm+1 end;
        end loop;

        if rec.next is null then
            WKYYYYmm := rec.yyyymm;
            WKYYYYmm := case mod(WKYYYYmm,100) when 12 then WKYYYYmm -12+101 else WKYYYYmm+1 end;

            while (WKYYYYmm <= rec.maxYM) Loop
                DBMS_Output.Put_Line(rec.Code || '■' || WKYYYYmm || '■0');
                WKYYYYmm := case mod(WKYYYYmm,100) when 12 then WKYYYYmm -12+101 else WKYYYYmm+1 end;
            end loop;

            firstEachCode := true;
        end if;
    end Loop;
end;
/

解説

PL/SQLのDBMS_Output.Put_Line版を作成後に
表関数化するといいでしょう。

---