Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

祝日、休日カレンダーの活用例（締め日、支払期限＆営業日 ⇒ 支払い日）

⇒ 手動で作った祝日・休日一覧、または、プログラム・メンテナンスが不要な休日カレンダー [holiday_list] が必要。

取引先、支払い単位ごとに集計したリストにカレンダーをマッチさせる

取引先、支払い単位ごとに集計し、支払期限までに支払ができる日にちを算出する。
支払日は支払期限までに完了するように自社の営業日中かつ金融機関の営業日を考慮すること。

祝日・休日（営業日） 一覧作成に必要なプログラム

現実的なサンプル 取引先別の締め日、営業日を考慮した支払日一覧

• 締め日と支払期限の算出（１）
    指定した日付の締め日または支払日を取得するファンクションの定義

• 日本の休日と営業日カレンダーのマージ（２）
    日本の祝日・休日、銀行公休日、自社の休日の和集合を日にちの重複なしで取得

• カレンダーとのパターンマッチ 営業日の算出（３）
    休日一覧から支払期限に間に合う最終支払日を営業日の中から取得する

• 取引先、支払期限別の支払リスト（４）
    取引先ごとの支払条件にあわせた支払合計額リストを取得

支払いリストの作成 Oracle 12c 以降

インラインのファンクション定義、行パターン・マッチング検索は Oracle 12c 以降の機能

with
 function due_date ( ---- 締め日と支払期限の算出（１）
   dd date, closing_dd positive := NULL, usance_dd positive := NULL, usance_mm natural:= 1
 )
 return date
 is vDate DATE;
 begin
  if (closing_dd is null) then return dd; end if;  -- 締め日がない場合には当日
  vDate := 
    case when extract(day from dd) <= closing_dd then add_months(trunc(dd, 'mm'), usance_mm)
    else add_months(trunc(dd, 'mm'), usance_mm + 1)
  end;
  return least(vDate + nvl(usance_dd, closing_dd) - 1, last_day(vDate));
  end;
  --- query
  ---- 日本の休日と営業日カレンダーのマージ（２）
 merge_holiday as (
   select dd from (
     select row_number() over (
       partition by dd order by null  -- 並び順はどうでもよい
     ) rn, dd
     from holiday_list 
     where  -- 公休日、日本の祝休日、自社の休日リストの一覧、0：平日（記念日）を除く
       primary_tag in ('JP', 'JP-BANK', 'OWN-HOLIDAY') and date_type <> 0 
   )
   where rn = 1
 ),
 -- match_recognize - Oracle 12c 
 ---- カレンダーの行パターンマッチ 営業日の算出（３）
 move_dd_map as (
  select dd, moveup_dd
  from merge_holiday h
  match_recognize (                -- パターンマッチ検索定義
    order by dd                    -- 日付の昇順でパターン一致探索
    measures s.dd -1 as moveup_dd  -- パターン一致開始日の前日にカラム名を付与
    all rows per match             -- 一致するパターン行をすべて抽出
    pattern( s n* )                -- 探索のパターン s: 常に真(未定義)、n: 0件以上の一致。
    define n as dd - prev(dd) = 1  -- n の定義：日付の昇順で日にち(dd)が連続しているもの。
  )    -- ↑ s[tart] や n[ext] を自由に命名やより高度なパターンも定義できる
 ),
 ---- 取引先、支払期限別の支払リスト（４）
 pay_list as (
   select tr.id, sum(tr.price) price,
     due_date(tr.dd, py.closing_dd, py.usance_dd, py.usance_mm) due_dd
   from client_trade tr
   join client_payment py on ( py.id = tr.id )
   group by tr.id, due_date(tr.dd, py.closing_dd, py.usance_dd, py.usance_mm)
 )
---- 取引先ごとの月別集計または即時の支払リスト
select
  pl.id, cp.client_name "取引先名",  pl.price "取引金額",
  to_char(pl.due_dd, 'yyyy-mm-dd(dy)') "支払期限",
  nvl2(cp.closing_dd, nvl2(m.dd, m.moveup_dd, pl.due_dd), pl.due_dd) "支払日",
  cp.payment_term "支払条件"
from pay_list pl
join client_payment cp on cp.id = pl.id
left join move_dd_map m on m.dd = pl.due_dd
order by pl.due_dd, pl.id
/
 
   ID 取引先名           取引金額 支払期限         支払日     支払条件
----- ---------------- ---------- ---------------- ---------- --------
 2525 ウシウシ金融            500 2018-10-25(木)   2018-10-25 銀行振込
 4949 メーメー商会              5 2018-11-03(土)   2018-11-03 都度払い
 4949 メーメー商会              3 2018-11-21(水)   2018-11-21 都度払い
 2525 ウシウシ金融           3000 2018-11-25(日)   2018-11-22 銀行振込

支払いリストの作成 Oracle 12c より前

with
  ---- 日本の休日と営業日カレンダーのマージ（２）
 merge_holiday as (
   select dd from (
     select row_number() over (
       partition by dd order by null  -- 並び順はどうでもよい
     ) rn, dd
     from holiday_list 
     where  -- 公休日、日本の祝休日、自社の休日リストの一覧、0：平日（記念日）を除く
       primary_tag in ('JP', 'JP-BANK', 'OWN-HOLIDAY') and date_type <> 0 
   )
   where rn = 1
 ),
 ---- カレンダーとのパターンマッチ 営業日の算出（３）
 move_dd_map as (
  select
    dd, first_value(dd) over (partition by tuple_no order by dd) - 1 moveup_dd
  from (
    select dd,
     sum(seq_flg) over (order by dd) tuple_no
     -- tuple_no = match_recognize の measures match_number() 相当
    from (
      select dd, 
        case when (dd - lag(dd) over (order by dd) = 1) then 0 else 1 end seq_flg
      from merge_holiday
    )
  )
 ),
 ---- 取引先、支払期限別の支払リスト（４）、支払期限の算出（１）上の due_date  関数と同じ内容
 pay_list as (
   select id, sum(price) price, due_dd
   from (
     select
       tr.dd, tr.id, tr.price,
       nvl2( py.closing_dd, 
         add_months(trunc(dd, 'mm'), usance_mm) - 1 +
         least(
           extract(
             day from
             last_day(add_months(trunc(dd, 'mm'),
               usance_mm + case when extract(day from dd) <= py.closing_dd then 0 else 1 end)
             )
           ), usance_dd
         ),
         tr.dd
       ) due_dd
     from client_trade tr
     join client_payment py on ( py.id = tr.id )
   )
   group by id, due_dd
 )
---- 取引先ごとの月別集計または即時の支払リスト
select
  pl.id, cp.client_name "取引先名",  pl.price "取引金額",
  to_char(pl.due_dd, 'yyyy-mm-dd(dy)') "支払期限" ,
  nvl2(cp.closing_dd, nvl2(m.dd, m.moveup_dd, pl.due_dd), pl.due_dd) "支払日",
  cp.payment_term "支払条件"
from pay_list pl
join client_payment cp on cp.id = pl.id
left join move_dd_map m on m.dd = pl.due_dd
order by pl.due_dd, pl.id
;
 
   ID 取引先名           取引金額 支払期限         支払日     支払条件
----- ---------------- ---------- ---------------- ---------- --------
 2525 ウシウシ金融            500 2018-10-25(木)   2018-10-25 銀行振込
 4949 メーメー商会              5 2018-11-03(土)   2018-11-03 都度払い
 4949 メーメー商会              3 2018-11-21(水)   2018-11-21 都度払い
 2525 ウシウシ金融           3000 2018-11-25(日)   2018-11-22 銀行振込

データ・テーブル定義

with 内部のクエリーを 上記の SQL に移動可能

create table client_payment as
with
 client_payment(id, client_name, closing_dd, usance_dd, usance_mm, payment_term) as (
   select 2525, 'ウシウシ金融', 31, 25, 1, '銀行振込' from dual union all
   select 4949, 'メーメー商会', null, null, null, '都度払い' from dual
 )
select * from client_payment ;
create table client_trade as
with
 client_trade(id, price, dd) as (
   select 2525, 500, date '2018-09-01' dd from  dual union all
   select 2525, 1000, date '2018-10-01' dd from  dual union all
   select 2525, 2000, date '2018-10-20' dd from  dual union all
   select 4949, 5 , date '2018-11-03' dd from  dual union all
   select 4949, 3 , date '2018-11-21' dd from  dual
 )
select * from client_trade;

---

関連事項

• 各国語対応可能な祝日、休日一覧を作成する