売上日より小さく且つ最大日の抽出

質問

以前こちらで商品の価格変更に対応するため履歴テーブルの作成を教えていただきました。作成したテーブルは以下のようなものです。
商品番号,価格,開始日,終了日
00000001,120,2006/01/01,9999/09/09
00000002,200,2006/01/01,2006/12/31
00000002,210,2007/01/01,9999/09/09
00000003,150,2006/01/01,2006/09/30
00000003,180,2006/10/01,9999/09/09

売上テーブルと履歴テーブルを使い売上金額を集計しています。
商品番号が複数存在する場合は売上日が開始日と終了日の間になる価格を取ってくるようにしています。
しかし現在、履歴テーブルの終了日は手入力しているため、この手間を省くため以下のようなテーブルへの変更を検討しています。

商品番号,価格,適用日
00000001,120,2006/01/01
00000002,200,2006/01/01
00000002,210,2007/01/01
00000003,150,2006/01/01
00000003,180,2006/10/01

このような履歴テーブルにした場合、商品番号が複数存在するときは売上日より小さく且つ最大日のものを抽出するようにしなければならないと思うのですが、うまくいかず売上日より小さい日付ものか最大日付のもののどちらかしか抽出できません。
どのようにしたらいいのでしょうか。
よろしくお願いします。

回答No.2

nikunikuman · ベストアンサー

sqlが間違えていましたね。最後は[asc]ではなく、[desc]になりますね。

で、フォームとかクエリとかかかれているので、ms accessで実現したいということですね？
先頭行を取得するにはTOPという構文が使えたかと思います。
select top 1 ....みたいに書けば、先頭行を取得できるかと思いますが、いかがでしょうか？　

コメントですが、基本的に売上テーブルのような業務系トランザクション系のテーブルでは
その時の価格を逐次記録した方がよいです。何かのタイミングで間違えて価格や適用日が変わってしまうことを考えると、全ての売上金額に影響を受けることになります。つまり、
売上テーブルは以下の構成にしておく方がお勧めです。
売上日,商品番号,売上数,売上単価,売上金額

回答No.4

noname#246547

DBMSが判らないので通用するかどうか保障しませんが、下記のSQLはどうですか？

select 商品番号,売上数,(※) as kakaku
from 売上テーブル as u
where u.日付 = 'フォームで入力した日';

※の部分（ごちゃごちゃするので分けました）
select 価格
from 履歴テーブル as r
where r.商品番号 = u.商品番号
and r.適用日 <= u.日付
order by r.適用日 desc limit 1

サブクエリーで「日付」以前でもっとも新しい「価格」を1件取得すればいいんじゃないかな？

回答No.3

noname#182251

>売上日より小さく且つ最大日のもの
が、トリッキーな構造だと考えます。
「売上テーブル」に「履歴テーブル」の商品価格ＩＤフィールドを追加すれば、扱いも簡単だし、関係も明瞭になるでしょう。

回答No.1

nikunikuman

sqlの質問になるのでしょうか？
sqlならば、

select
履歴テーブル.価格,
履歴テーブル.適用日
from
履歴テーブル
where
履歴テーブル.商品番号=xxxxx
and
履歴テーブル.適用日<= 売上日
order by
履歴テーブル.適用日 asc;

で取得できる一番最初のレコードを取得すると、ご希望の価格が取れると思います。

お礼

済みません。私の説明が悪かったようで質問の意図が伝わらなかったと思います。
履歴テーブル
商品番号,価格,適用日
00000001,120,2006/01/01
00000002,200,2006/01/01
00000002,210,2007/01/01
00000003,150,2006/01/01
00000003,180,2006/10/01

売上テーブル
　日　付　,商品番号,売上数
2006/09/30,00000004, 200
2006/09/30,00000005, 20
2006/10/01,00000001, 100
2006/10/01,00000002, 10
2006/10/02,00000001, 80
2006/10/02,00000003, 120
2006/10/03,00000005, 50

上記の２つのテーブルからフォームで日付を入力してクエリを実行し日ごとの売上金額を求めたいと思っています。
このとき履歴テーブルに同一商品番号が複数存在する場合、売上日より小さく且つ最大日の価格で計算しなければならないのですが、それをどのように実現したらいいのかが分かりません。
よろしくお願いします。