(再度SQLのご指導を願います)
先日にこちらの掲示板でご親切な方からアドバイスをいただき
試した結果、以下の通り若干の不具合が発生し、再度ご質問する次第です。
恐縮ながら以下にご教授いただければ幸いです。
テーブルは以下で仮定します。
・注文テーブル
購入日 レジコード 顧客コード 顧客種別 商品NO購入金額 購入個数 年代
2011/01/01 111111 123456 通常 100 ¥100 1 20代
2011/01/02 111112 129999 通常 100 ¥300 3 50代
2011/01/02 111112 129999 通常 200 ¥200 1 50代
2011/01/02 111112 129999 通常 500 ¥500 2 50代
2011/02/01 111113 122222 新規 100 ¥100 1 90代
2011/02/05 111114 133333 新規 400 ¥400 1 10代
2011/02/11 111115 144444 通常 1000 ¥1,000 1 30代
2011/02/11 111115 144444 通常 100 ¥100 1 30代
2011/03/01 111116 155555 新規 100 ¥300 3 20代
2011/03/01 111116 155555 新規 200 ¥400 2 20代
2011/03/05 111117 166666 通常 500 ¥250 1 30代
2011/03/15 111118 177777 通常 400 ¥800 2 40代
2011/03/16 111119 188888 通常 100 ¥100 1 50代
2011/03/16 120000 123456 通常 100 ¥1,000 10 20代
2011/03/16 120000 123456 通常 400 ¥400 1 20代
2011/03/16 120000 123456 通常 200 ¥400 2 20代
2011/03/30 121000 144444 通常 100 ¥300 3 30代
2011/03/30 121000 144444 通常 1000 ¥3,000 3 30代
2011/03/30 121000 144444 通常 500 ¥750 3 30代
・割引テーブル
商品NO 商品名開始日終了日
100 ごはん 2011/03/01 2011/03/30
100 ごはん 2011/01/01 2011/01/31
抽出したい内容
(1)注文テーブルにある購入日で日付を指定の上、（betweenで2010/06/01-2011/05/31までなど）
商品NOを特定の1品目、もしくは複数選択をした上で、その商品に対する期間内の購入数・購入金額を求めたい。
ただし、"割引テーブル"に差し掛かる期間のみ、別の列で分けて表示させたい。
（つまり割引期間と通常期間を列で分けることで売上の差を見極めたいのです。）
これを以下の構文で試してみました。
SELECT t.購入日, t.商品NO,
Sum(iif(t.購入日<w.開始日 or t.購入日>w.終了日,t.購入個数,0)) AS 通常購入,
Sum(Switch(t.購入日<w.開始日 Or t.購入日>w.終了日,0,True,t.購入個数)) AS 割引購入,
Sum(Switch(t.購入日<w.開始日 Or t.購入日>w.終了日,t.購入金額,True,0)) AS 通常購入金額,
Sum(Switch(t.購入日<w.開始日 Or t.購入日>w.終了日,0,True,t.購入金額)) AS 割引購入金額
FROM 注文テーブル AS t INNER JOIN 割引テーブル AS w ON t.商品NO = w.商品NO
WHERE (((t.商品NO)=100) AND ((t.購入日) Between #1/1/2011# And #3/30/2011#))
GROUP BY t.購入日, t.商品NO;
実行結果
購入日 商品NO 通常購入 割引購入 通常購入金額 割引購入金額
2011/01/01 100 1 1 ¥100 ¥100
2011/01/02 100 3 3 ¥300 ¥300
2011/02/01 100 2 0 ¥200 ¥0
2011/02/11 100 2 0 ¥200 ¥0
2011/03/01 100 3 3 ¥300 ¥300
2011/03/16 100 11 11 ¥1,100 ¥1,100
2011/03/30 100 3 3 ¥300 ¥300
上記のようになりました。
上記の割引テーブルで仮定すると、1月と3月の売り上げは”割引購入"と"割引購入金額"に記載され、2月の分は"通常購入"と"通常購入数"にその時の売り上げを出したいと考えております。
現在の結果の場合、割引テーブルで設定された割引期間内の"通常購入"と"通常購入数"にも数字が入ってしまっています、、。
理想の抽出結果は以下の通りです。
購入日 商品NO 通常購入 割引購入 通常購入金額 割引購入金額
2011/01/01 100 0 1 ¥0 ¥100
2011/01/02 100 0 3 ¥0 ¥300
2011/02/01 100 2 0 ¥200 ¥0
2011/02/11 100 2 0 ¥200 ¥0
2011/03/01 100 0 3 ¥0 ¥300
2011/03/16 100 0 11 ¥0 ¥1,100
2011/03/30 100 0 3 ¥0 ¥300
（2)注文テーブルにある購入日で日付を指定の上、（上記(1)と同様）商品NOを特定の1品目、もしくは複数選択をした上で、年代別で顧客種別ごとの客数を知りたい場合
※上の客数は顧客種別かレジコードをカウントすれば良いと思いますが、一人のお客様が一回の会計で5品目買ったらレコードは5つになるため、重複を防ぐ必要があります。（対象の品物を何回購入したことがあるか表示したいので、、）
また、"顧客種別"は通常・新規・フリーの三種類の言葉で分類分けされて登録されてますが、通常のみ、設定した抽出対象期間前にも1回でも購入があれば"リピート"としてカウント、過去に購入なく抽出対象期間内で初めて購入があった場合は"初めて"にカウントしたいです。
これを以下構文で試してみました。
SELECT
t.商品NO
, t.年代
, sum(switch(t.顧客種別='通常' and k.顧客コード is not null, 1 , true ,0)) as リピート
, sum(switch(t.顧客種別='通常' and k.顧客コード is not null, 0 , true ,1)) as 初めて
, sum(switch(t.顧客種別='新規' , 1 , true ,0)) as 新規
, sum(switch(t.顧客種別='フリー', 1 , true ,0)) as フリー
FROM 注文テーブル t
LEFT JOIN
(SELECT 顧客コード
FROM 注文テーブル
WHERE 購入日<#2011/02/28#) k
ON t.顧客コード = k.顧客コード
WHERE t.購入日 between #2011/03/01# and #2011/03/31#
and t.商品NO = １00
GROUP BY t.商品NO, t.年代
抽出結果
商品NO 年代 リピート 初めて 新規 フリー
100 20代 1 1 1 0
100 30代 2 0 0 0
100 50代 0 1 0 0
たとえば、3月1日～3月31日までの期間で抽出をすると
商品NO100の商品を買った30代のリピートの方は1名（顧客NO144444）だけに
なると思いますが、抽出結果では2名になってしまいます。
・対象の商品"100"を設定した対象期間内（上記の構文でいうと2011/03/01-2011/03/31)
でしか購入がなかった場合は"初めて"としてカウント
・対象の商品"100"を設定した対象期間内（上記の構文でいうと2011/03/01-2011/03/31)
で購入があり、且つその設定日付以前にも同じ商品の購入があった場合は"リピート"として
カウント（設定日以前に同じリピート客が同じ商品を100回買ったとしても、100カウントではなく１カウント）リピートのお客様が"何回買ったか？"ではなく、"リピートしてる人が何人いるか？"を表したいのです。
これがが出来ないか頑張っています、、。
一番上のテーブルを仮定して理想の抽出結果は
商品NO 年代 リピート 初めて 新規 フリー
100 20代 1 0 1 0
100 30代 1 0 0 0
100 50代 0 1 0 0
お手数をおかけしますが、なにとぞご教授をお願いいたします。
お礼
回答ありがとうございます。 お世話になりました。デバッグとおりました。