10営業日前の日付を取得したい（修正）

質問

※連投すみません。削除も修正もできなかったので連投になってしまいました。
一番重要な説明をコピペミスしてしまいました。
明日になれば削除できるようなので古い方は明日削除致しますm(__)m

---ここから---
今日は9/24という想定で各部署の10営業日前の日付を取得したいのですが良い方法が思いつきません。
（ここ↓を参考にしたらできそうな気がしましたが私の頭では無理でした。。。）
http://okwave.jp/qa/q6677209.html

【休日テーブル】
部署コード,　休日
-----------------
busyo1,　　　9/13
busyo1,　　　9/14
busyo1,　　　9/20
busyo1,　　　9/21
busyo1,　　　9/23
busyo2,　　　9/7
busyo2,　　　9/14
busyo2,　　　9/21
busyo3,　　　9/20

【結果】(各部署の10営業日前の日付)
部署コード,　日付
-----------------
busyo1,　　　9/9
busyo2,　　　9/12
busyo3,　　　9/13

このような「発想の転換」が必要な感じのSQLが本当に苦手です。
全然思いつかずに今日も半日を浪費してしまいました。
何かコツのようなものがあるのでしょうか。

お礼は明日の夜になってしまいますが何卒よろしくお願いします。

回答No.2

yamada_g · ベストアンサー

あまり深く考えていませんので、件数が多いと使いものにならないかもしれません。

with 休日テーブル as (
select 'busyo1' 部署コード, date'2014-09-13' 休日 from dual
union all select 'busyo1' 部署コード, date'2014-09-14' 休日 from dual
union all select 'busyo1' 部署コード, date'2014-09-20' 休日 from dual
union all select 'busyo1' 部署コード, date'2014-09-21' 休日 from dual
union all select 'busyo1' 部署コード, date'2014-09-23' 休日 from dual
union all select 'busyo2' 部署コード, date'2014-09-07' 休日 from dual
union all select 'busyo2' 部署コード, date'2014-09-14' 休日 from dual
union all select 'busyo2' 部署コード, date'2014-09-21' 休日 from dual
union all select 'busyo3' 部署コード, date'2014-09-20' 休日 from dual
)
, 日付 as (
select trunc(/* sysdate */ date'2014-09-24' - 1) - level 日付
from dual
--とりあえず過去30日分の日付
connect by level <= 30)
select 部署コード, 日付
from (
select 部署コード, 日付, count(*) over(partition by 部署コード order by 日付 desc) cnt
from (select distinct 部署コード from 休日テーブル) 部署マスタ
cross join 日付
where not exists (
select * from 休日テーブル where 部署マスタ.部署コード = 休日テーブル.部署コード and 日付.日付 = 休日テーブル.休日
)
)
where cnt = 10;

こまかい説明を書く時間がないので、そこはSQLから読みとってください。すみません・・・

お礼

いえいえ、SQLから自分で読み解くのは当たり前だと思っています。
教えていただけただけでも大変ありがたく感じております。
さすがにはじめて見る記述だらけで解読に手間取りましたが
おかげさまで無事に一日を終えることができました。
ありがとうございました。

回答No.1

bin-chan

元のデータは何件あるんですか？
レコード数ではなくて、日付の数。

Excelにでもコピペして、Update文を作るほうが簡単かも。

補足

元のデータの件数は過去20年分は入っているそうなので1部署あたり約2400件です。
（もう少し少ない部署もあります）
ちなみに部署は約100種類あります。

アドバイス頂いた「Excelにコピペ」という意味がよくわからずに書いてしまいますが、このSQLは大規模なシステムの1部で使われます。
その画面の１つのそのまた１つの項目に使われるものなので、もしユーザ様にExcelを使わせるような想定をされているのならそれは無理なのです。
（少し寝たらひらめくかな、と期待しましたが朝になっても全くひらめきません。困りました。。）