SQLで1レコードを複数レコードとして抽出したい
例えばスケジュールを管理しているテーブルとして
期間を保持しているテーブルA
(場所AAAは9:00から18:00時まで開いている)
日付 | 場所CD | 開始時刻 | 終了時刻 |
---|---|---|---|
2017/09/01 | AAA | 9:00 | 18:00 |
予定を保持しているテーブルB
(担当者001は場所AAAを10:00から12:00まで利用する。担当者002は場所AAAを14:00から17:00まで利用する。)
|日付|場所CD|担当者CD|開始時刻|終了時刻|
|:--|:--|:--|:--|
|2017/09/01|AAA|001|10:00|12:00|
|2017/09/01|AAA|002|14:00|17:00|
とあった場合
SQLのみで以下のような、場所が空いているデータを取得できますでしょうか?
日付 | 場所CD | 開始時刻 | 終了時刻 |
---|---|---|---|
2017/09/01 | AAA | 9:00 | 10:00 |
2017/09/01 | AAA | 12:00 | 14:00 |
2017/09/01 | AAA | 17:00 | 18:00 |
※DBはSQL Serverを使用していおります。
気になる質問をクリップする
クリップした質問は、後からいつでもMYページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/08/29 07:20
回答3件
0
うーん、CTEを使ってがんばって出してみましたが、かなり冗長な気がします……。
もっと良いやり方がありそうです。
※日付と場所CDは無視しているので、適宜修正してください
sql
1/* データをとりあえずテーブル変数で用意 */ 2declare @tblA table ( 3 日付 date, 4 場所CD char(3), 5 開始時刻 time, 6 終了時刻 time 7) 8insert into @tblA values('2017-09-01','AAA','09:00:00','18:00:00') 9 10declare @tblB table ( 11 日付 date, 12 場所CD char(3), 13 担当者CD char(3), 14 開始時刻 time, 15 終了時刻 time 16) 17insert into @tblB values('2017-09-01','AAA','001','10:00:00','12:00:00') 18insert into @tblB values('2017-09-01','AAA','002','14:00:00','17:00:00') 19 20; 21/* opentime:利用可能時間帯を30分刻みで列挙 */ 22with opentime as ( 23 select 24 開始時刻 時刻 25 from 26 @tblA 27 union all 28 select 29 dateadd(minute,30,時刻) 時刻 30 from 31 opentime 32 where 33 時刻<(select dateadd(minute,-30,終了時刻) from @tblA) 34), 35/* opentime2:利用中の時間帯を除外した結果 */ 36opentime2 as ( 37 select 38 O.時刻, 39 case when lead(O.時刻,1) over(order by O.時刻)=dateadd(minute, 30,O.時刻) then 1 else 0 end f, 40 case when lag(O.時刻,1) over(order by O.時刻)=dateadd(minute,-30,O.時刻) then 1 else 0 end t 41 from 42 opentime O 43 where 44 not exists ( 45 select * from @tblB B 46 where 47 B.開始時刻<=O.時刻 and O.時刻<B.終了時刻 48 ) 49), 50/* opentime3:開始と終了のみを抽出する準備 */ 51opentime3 as ( 52 select 53 O2.時刻 開始時刻, 54 case when t=0 then lead(O2.時刻,1) over(order by O2.時刻) else null end 次開始時刻 55 from opentime2 O2 56 where O2.f=0 or O2.t=0 57) 58/* 最終結果 */ 59select 60 O3.開始時刻, 61 dateadd(minute,30,O3.次開始時刻) 終了時刻 62from opentime3 O3 63where 64 O3.次開始時刻 is not null
実行結果:
開始時刻 | 終了時刻 |
---|---|
09:00:00.0000000 | 10:00:00.0000000 |
12:00:00.0000000 | 14:00:00.0000000 |
17:00:00.0000000 | 18:00:00.0000000 |
投稿2017/08/29 10:13
編集2017/08/29 10:14総合スコア2019
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/08/29 10:29
2017/08/30 02:03 編集
2017/08/30 01:59
0
ベストアンサー
こんな感じでどうでしょうか。
概略としては、
・テーブルAからその日の予定の開始~終了以外の両端の空き時間を求める。
・テーブルBから、lag()によって前回の終了時刻を求め、それを空の開始時刻、開始時刻を終了時刻とする。
(最初の空き時間はテーブルAから求めているので、NUll判定で除去)
・上記をUNIONでマージ(予定が無い場合はUNIONでマージされて、結果、テーブルAと同じになる)
SQL
1 select 日付, 場所CD 2 , 開始時刻 3 , coalesce((select min(開始時刻) from tblb where 場所CD=ta.場所CD and 日付=ta.日付), 終了時刻) as 終了時刻 4 from tbla as ta 5 where 開始時刻<((select min(開始時刻) from tblb where 場所CD=ta.場所CD and 日付=ta.日付)) 6union 7 select 日付, 場所CD, 前回終了時刻, 開始時刻 8 from (select *, lag(終了時刻,1) over(partition by 場所CD, 日付 order by, 開始時刻) as 前回終了時刻 from tblb) tmp 9 where 前回終了時刻 is not null 10union 11 select 日付, 場所CD 12 , coalesce((select max(終了時刻) from tblb where 場所CD=ta.場所CD and 日付=ta.日付), 開始時刻) 13 , 終了時刻 14 from tbla as ta 15 where 終了時刻>((select max(終了時刻) from tblb where 場所CD=ta.場所CD and 日付=ta.日付)) 16order by 場所CD,日付,開始時刻
手っ取り早くunionにしていますが、時間がかかるようなら除外条件(予定が無い場合)をいれて、union allとすれば、unionマージの時間は省けます。
投稿2017/08/30 01:25
編集2017/08/30 04:05総合スコア25173
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/08/30 02:02
2017/08/30 02:38
0
2017/09/01 AAA 9:00 18:00
と入力されたら(場所や日付は複数可)30分(設定を見る)ごとにデータを作る。
2017/09/01 AAA 001 10:00 12:00
と入力されたら指定区間のデータに印(使用中フラグ)をつける。
で、使用中フラグの付いていないデータを抜き出せばOK。
画面は作らねばなりませんがデータの整合性の問題もありますし(データの手入力なら
10分単位でも開始>終了など何でもありになってしまう)この方がいいかと。
投稿2017/08/29 09:14
総合スコア876
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/08/30 01:53
あなたの回答
tips
太字
斜体
打ち消し線
見出し
引用テキストの挿入
コードの挿入
リンクの挿入
リストの挿入
番号リストの挿入
表の挿入
水平線の挿入
プレビュー
質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。