読者です 読者をやめる 読者になる 読者になる

ソフトウェア開発者の日常

こだわりなく書きたいことを書いていきます。

SQL:日付範囲の比較で四苦八苦した

SQL プログラミング
スポンサーリンク

考え始めたときは、CASE文を使うとは思っていなかった。

f:id:AJYA:20120828214158j:plain

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で作りこみができれば、プログラム側が簡素にできるので、データベース毎のできることを把握しておくことは重要だ。