sql 未来価格がある場合は未来価格を表示、現在価格がある場合は現在価格を表示したい。

Question

現在、賞品一覧に対して１つだけ価格を表示したいのですが、
なかなかクエリが決まらず困っています。
どなたかお力添えをお願い致します。

■現状
賞品マスタ、賞品情報マスタ、賞品価格マスタという３つのテーブルがあり、
１つの賞品に対して、新しく価格ができるたびに賞品情報マスタと賞品価格マスタが１つずつ作成されていきます。
賞品情報マスタは賞品マスタIDを持っており、賞品価格マスタは賞品情報マスタIDを持っています。
賞品価格マスタには、最新価格であればたつ最新価格フラグを持っています。

パターン１：現在日時が20161201の場合に下記のような２つの価格がある場合は、現在適応範囲の価格１を表示します。
価格１：100円 開始日20160401 終了日20170331
価格２：200円 開始日20170401 終了日99991231

賞品一覧表示結果：100円

パターン２：現在日時が20161201だが、価格が未来から始まるものしかない場合はその価格を表示します。
価格１：300円 開始日20170401 終了日99991231

賞品一覧表示結果：300円

１つのViewを用いてこのように制御したいのですが、
どう書いたらいいかわからず困っています。

select *
from 賞品マスタ a
inner join 賞品情報マスタ b on a.id = b.賞品id
inner join 賞品価格マスタ c on b.id = c.賞品情報id
where sysdatetime() between 価格開始日 and 価格終了日

こう書くと、未来価格しかないものがこぼれてしまいます。
かといって

select *
from 賞品マスタ a
inner join 賞品情報マスタ b on a.id = b.賞品id
inner join 賞品価格マスタ c on b.id = c.賞品情報id
where (sysdatetime() between c.価格開始日 and c.価格終了日)
or c.価格開始日 > sysdatetime()

と書くと、未来価格と現在価格どちらもある場合に、１つの賞品に対して２つの価格が出てしまうのでNGです。

現在価格がある場合は現在価格を、未来価格のみの場合は未来価格を表示したいです。

どなたか宜しくお願い致します。

Answer 1

動作確認してないし、これより良い方法がある気がしますので参考程度に。

select *,
   RANK() OVER (
       PARTITION BY a.id, b.商品情報id
       ORDER BY
           CASE WHEN c.価格開始日 > sysdatetime() THEN 1 ELSE 0 END 未来日付フラグ ASC,
           c.価格開始日 DESC
   ) AS 日付優先順位
from 賞品マスタ a
inner join 賞品情報マスタ b on a.id = b.賞品id
inner join 賞品価格マスタ c on b.id = c.賞品情報id
where (sysdatetime() between c.価格開始日 and c.価格終了日)
or c.価格開始日 > sysdatetime()

ご提示の2つ目のクエリのSELECT部分に、RANKの部分を追加しています。この部分を簡単に説明すると、同一商品(a.id,b.商品情報idの組み合わせが同じもの)内で、現在日付を優先的にした順位付けをするといったものです。

ご提示のパターン1の場合は
価格１：100円 開始日20160401 終了日20170331 日付優先順位1
価格２：200円 開始日20170401 終了日99991231 日付優先順位2
のような行が取得できます。

パターン2のような1行しか無い場合は
価格１：300円 開始日20170401 終了日99991231 日付優先順位1
といった感じです。

あとは上記のSQLをサブクエリとして、そこから日付優先順位が1のレコードを抜き出せばいいです。

select
    * --アスタリスクだと日付優先順位列も取れてしまうため、本当は列1つ1つ指定する必要があるけど...
from (
    select *,
      RANK() OVER (
         PARTITION BY a.id, b.商品情報id
         ORDER BY
           CASE WHEN c.価格開始日 > sysdatetime() THEN 1 ELSE 0 END 未来日付フラグ DESC,
             c.価格開始日 DESC
     ) AS 日付優先順位
    from 賞品マスタ a
    inner join 賞品情報マスタ b on a.id = b.賞品id
    inner join 賞品価格マスタ c on b.id = c.賞品情報id
    where (sysdatetime() between c.価格開始日 and c.価格終了日)
    or c.価格開始日 > sysdatetime()
) T
where T.日付優先順位 = 1

Comments

[amakusa]

この回答を応用してやったら見事にできました！
感謝しかありません！ありがとうございます！！！！
優先順位をつける、というのは知らなかったのでとても勉強になりました！