何番煎じかわからないですが、自分用のメモ
予約のチェックのために 「指定した期間中に予約のあるレコードを抽出する」
などをしたいことがあります。
この時、レコード側の予約期間と 検索条件の日付期間をつきあわせてほしいものを取得することになります。
先に結論
先に結論を書くと
where
レコードの開始日付 <= 検索条件の終了日付
and レコードの終了日付 >= 検索条件の開始日付
でとれます。
もちろん仕様によっては 日付だけでなく 時刻も入ってきたり するので
<= や >= ではなく < や > にしたり、検索条件の日付を +1 したりする場合もあります。
あとnullが絡んでくる場合はその対応も必要です。
今回は 日付部分だけの単純パターンです。
考え方
データベース側のテーブルデータと、検索条件の関係を考えると
以下のパターンが考えられます
- 検索期間がレコード期間より前
- 検索期間がレコードの開始を含む
- 検索期間がレコードの期間に含まれる
- 検索期間がレコードの終了を含む
- 検索期間がレコード期間より後
- 検索期間がレコードの期間を含む
文章で説明を書いてみましたが イメージでとらえたほうがわかりやすいかと思います。
このうち、期間の重なりがあるのは 赤色で示した部分になります。
緑色の部分の否定で取りに行く方法もあるかもしれないですが、今回は普通に赤色の部分を抽出しにいきます。
レコードの終了日付に注目
レコード側の終了日付に着目します。
すると 検索条件の開始日付はいずれも 左側(つまり以前)にあることがわかります。
これをクエリで表現すると
検索条件の開始日付 <= レコードの終了日付
となります。
私は 左辺にテーブルの列を書くことが多いので左右を入れ替えて
レコードの終了日付 >= 検索条件の開始日付
としておきます。
これだと 1が余計ですね
レコードの開始日付に注目
レコード側の開始日付に着目します。
すると 検索条件の終了日付はいずれも 右側(つまり以降)にあることがわかります。
これをクエリで表現すると
レコードの開始日付 <= 検索条件の終了日付
これだと 5が余計ですね
両方の条件を組み合わせる
1, 5 は片方にしか存在しないのでAND で組み合わせることで 範囲の重なりをとることができます。
実際に試してみる
実際に試してみます。
データベースは H2 Database を使いました。
テーブル
こんな感じの BOOKING テーブルを用意します。
| 列名 | 型名 | メモ |
|---|---|---|
| BOOKING_ID | VARCHAR(6) | 主キー |
| START_DATE | DATE | not null |
| END_DATE | DATE | not null |
create table BOOKING (
BOOKING_ID VARCHAR(6)
,START_DATE DATE not null
,END_DATE DATE not null
,constraint PK_BOOKING primary key (
BOOKING_ID
)
);
データは1件だけ用意します。
検索条件の方をいろいろ変化させることで 確認してみます。
| BOOKING_ID | START_DATE | END_DATE |
|---|---|---|
| 000001 | 2021-01-01 | 2021-01-31 |
insert into BOOKING values ('000001', DATE'2021-01-01', DATE'2021-01-31');
実行
以下のようなクエリを実行してみます。
with を使っているのでわかりにくいですが SEARCH_CONDITION が検索条件で SEARCH_ID が今回の条件の番号です。
BOOKING 側ではなく、重なった検索条件の方を結果に表示するようにしています。
with
-- 変化させる検索条件を6パターン用意
SEARCH_CONDITION(SEARCH_ID, SEARCH_FROM, SEARCH_TO) as (
select 1, '2020-12-01', '2020-12-31' union all
select 2, '2020-12-15', '2021-01-15' union all
select 3, '2021-01-10', '2021-01-20' union all
select 4, '2021-01-15', '2021-02-15' union all
select 5, '2021-02-15', '2021-02-28' union all
select 6, '2020-12-01', '2021-02-28'
)
select
C.SEARCH_ID
,C.SEARCH_FROM
,C.SEARCH_TO
from
BOOKING B
cross join
SEARCH_CONDITION C
where
B.START_DATE <= TO_DATE(C.SEARCH_TO, 'YYYY-MM-DD')
and B.END_DATE >= TO_DATE(C.SEARCH_FROM, 'YYYY-MM-DD')
order by
C.SEARCH_ID
検索結果
| SEARCH_ID | SEARCH_FROM | SEARCH_TO |
|---|---|---|
| 2 | 2020-12-15 | 2021-01-15 |
| 3 | 2021-01-10 | 2021-01-20 |
| 4 | 2021-01-15 | 2021-02-15 |
| 6 | 2020-12-01 | 2021-02-28 |
2, 3, 4, 6が結果に上がってきているので正しくチェックできていることが確認できました。