考え始めたときは、CASE文を使うとは思っていなかった。
photo credit: therefromhere via photo pin cc
ことの発端
期間の判定で、6/1～7/15の間に今日の日付が入っているか判定するのは、SQLのWHEREで
6/1 <= 今日の年月 AND 今日の年月 <= 7/15
と書けばいい。
8/1～1/31(翌年を指している)の場合は、年があれば上記と同じでいいが、年がない場合は、単純には比較できない。
検討1
今年の2012を取得して、加算して翌年の2013を求めて、
2012/8/1 <= 今日の年月 AND 今日の年月 <= 2013/1/31
とすればできると考えた。
2013年になるとどうなるか考えたら、1/10の場合、
2013/8/1 <= 2013/1/10 AND 2013/1/10 <= 2014/1/31
となってしまい、条件が正しくないことがわかった。
検討2
検討1での駄目な点を改善するには、2012/8/27だったら、
(2012/1/1 <= 2012/8/27 AND 2012/8/27 <= 2012/1/31) OR (2012/8/1 <= 2012/8/27 AND 2012/8/27 <= 2012/12/31)
とすれば判定できる。
2013/1/10でも、同様に
(2013/1/1 <= 2013/1/10 AND 2013/1/10 <= 2013/1/31) OR (2013/8/1 <= 2013/1/10 AND 2013/1/10 <= 2013/12/31)
で正しく判定できることに気がついた。
実装
1回ではできないので、まず検討2の範囲の比較ができるように、開始と終了日を生成するビューを作った。
CASE文で、6/1～7/15のパターンか、8/1～1/31のパターンのどちらか判定して、日付型のカラムを加工したら、SQL Serverでの記述はこんな感じになった。
start1 = CASE WHEN SUBSTRING(CONVERT(char, start, 111), 6, 5) < SUBSTRING(CONVERT(char, end, 111), 6, 5) THEN SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/' + SUBSTRING(CONVERT(char, start, 111), 6, 5)
ELSE SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/01/01'
END,
end1 = CASE WHEN SUBSTRING(CONVERT(char, start, 111), 6, 5) < SUBSTRING(CONVERT(char, end, 111), 6, 5) THEN SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/' + SUBSTRING(CONVERT(char, end, 111), 6, 5)
ELSE SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/' + SUBSTRING(CONVERT(char, end, 111), 6, 5)
END,
start2 = CASE WHEN SUBSTRING(CONVERT(char, start, 111), 6, 5) < SUBSTRING(CONVERT(char, end, 111), 6, 5) THEN SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/' + SUBSTRING(CONVERT(char, start, 111), 6, 5)
ELSE SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/' + SUBSTRING(CONVERT(char, start, 111), 6, 5)
END,
end2 = CASE WHEN SUBSTRING(CONVERT(char, start, 111), 6, 5) < SUBSTRING(CONVERT(char, end, 111), 6, 5) THEN SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/' + SUBSTRING(CONVERT(char, end, 111), 6, 5)
ELSE SUBSTRING(CONVERT(char, GETDATE(), 111), 1, 4) + '/12/31'
END
このビューを使うビューを作って、そこで期間に入っているか判定をするようにした。
今回学んだこと
こういうことをするたびに思うが、SQLは「Structured Query Language」というだけあって、言語として様々な機能が提供されている。
SQLとは【Structured Query Language】 - 意味/解説/説明/定義 ： IT用語辞典
Structured Query Language ...
SQLで作りこみができれば、プログラム側が簡素にできるので、データベース毎のできることを把握しておくことは重要だ。
