前月の取得について

質問

テーブルAには、年月、単価と言うカラムを持っており、
データとしては、下記のように保持しております。
2011/01/01 100
2011/02/01 200
2011/03/01 300
2011/04/01 400

データを取得する際にWHERE句に「2011/02/01」と指定した場合に
「2011/02/01」を当月とし、
「2011/01/01」を前月として取得、
「2011/02/01」の単価と「2011/01/01」の単価を差分として取得したいのですが、
サブクエリだと複数のデータがあるため、エラーで落ちてしまいます。

何か良い取得方法があればご教授頂けないでしょうか。

SELECT
単価 as 当月,　（例：200)
単価 as 前月, (例：100)
単価 as 差分 (例：100)
FROM テーブルA WHERE 年月='2011/02/01'

以上、よろしくお願い致します。

回答No.3

_rainman · ベストアンサー

あまり難しいことはせず、SQL文は単純なものが望ましいです。

ちなみに以下のSQL文はデータが存在しない月は0(ゼロ)として
取り扱っていますので必要に応じて修正して下さい。

SELECT
[当月],
[前月],
([当月] - [前月]) AS [差分]
FROM
(
SELECT
ISNULL([当月],0) AS [当月],
ISNULL([前月],0) AS [前月]
FROM
(
SELECT
SUM([当月]) AS [当月],
SUM([前月]) AS [前月]
FROM
(
SELECT [単価] AS [当月], 0 AS [前月] FROM [テーブルA] WHERE [年月] = '2011/02/01'
UNION ALL
SELECT 0 AS [当月], [単価] AS [前月] FROM [テーブルA] WHERE [年月] = DATEADD(month ,-1 , CAST('2011/02/01' AS datetime))
) AS T0
) AS T1
) AS T2

お礼

回答ありがとうございます。

UNION ALLの必要性が私にはわからないので、調べてみます。
確認次第、ご連絡させて頂きます。

回答No.2

Tom-3

Select CONVERT(VARCHAR,A.[前月年月],112) AS 前月年月日
　　　,A.前月単価
　　　,CONVERT(VARCHAR,B.[当月年月],112) AS 当月年月日
　　　,B.当月単価
FROM
　　　(SELECT [年月] as 前月年月
　　　　　　,[単価] as 前月単価
　　　FROM [テーブルＡ]
　　　WHERE CAST(SUBSTRING(CONVERT(VARCHAR,[年月],112),1,6) as BIGINT)=
　　　　　　CAST(SUBSTRING(CONVERT(VARCHAR,DATEADD(month ,-1 , CAST('2011/02/01' as Datetime)),112),1,6) as BIGINT)) AS A,
　　　(SELECT [年月] as 当月年月
　　　　　　,[単価] as 当月単価
　　　FROM [テーブルＡ]
　　　WHERE CAST(SUBSTRING(CONVERT(VARCHAR,[年月],112),1,6) as BIGINT)=
　　　　　　CAST(SUBSTRING(CONVERT(VARCHAR,CAST('2011/02/01' as Datetime),112),1,6) as BIGINT)) AS B

の記述で可能かと思います。

ちなみに
１．CONVERT(VARCHAR,[年月],112)で
　　日付型(例:2011/02/01)を文字列型(例:20110201)に変換しています。

２．変換した文字列から必要なのは年月のみなので「SUBSTRING」で1桁目から
　　6桁目までの値を取得しています。
　　SUBSTRING('20110201',1,6)⇒'201102'

３．年月を取得した所でその値を正確に大小比較するために数値に変換しています。
　　CAST('201102' as bigint)⇒201102
４．次に指定する日付に関しては'2011/02/01'はただの文字列として処理されるので(SQL 2005)
　　'2011/02/01'を日付型に変換するためにCAST('2011/02/01' as Datetime)と
　　記載しています。　　
５．次に指定した日付のひと月前の日付処理はDATEADDという関数を使用します。
　　DATEADD(month ,-1 , CAST('2011/02/01' as Datetime)),112)
以上の点を考慮しつつ、クエリを作成すれば、指定された年月とひと月前のデータが出力されます。

お礼

ご回答ありがとうございます。

教えて頂いたSQLを理解するに少しお時間を下さい。。

頂いた回答を元にSQLを考察し、解決次第ご連絡させて頂きます。

回答No.1

Siegrune

質問文から素直に考えると年月がキーだから
select t1.単価 as 当月,t2.単価 as 前月,t1.単価 - t2.単価 as 差分
from
(SELECT 単価 FROM テーブルA WHERE 年月='2011/02/01') t1,
(SELECT 単価 FROM テーブルA WHERE 年月='2011/01/01') t2
注：'2011/01/01'は'2011/02/01'の1ヶ月前を関数でもとめるといいですが。
（SQL Serverの日付計算の関数を度忘れしてます。）
・・・年月が日付型かどうかによっても使う関数は変わりますが。
でも
select t1.単価 as 当月,
(SELECT 単価 FROM テーブルA WHERE 年月='2011/01/01') as 前月,
t1.単価 - (SELECT 単価 FROM テーブルA WHERE 年月='2011/01/01') as 差分
from テーブルA t1 WHERE 年月='2011/02/01'

でも問題なく、t1もt2も1件ずつしか取得しないように思えるのですが。

実際は、キー項目に商品コードがあって、直積になってしまうというのなら、
select t1.商品コード,t1.単価 as 当月,t2.単価 as 前月,t1.単価 - t2.単価 as 差分
from
(SELECT 商品コード,単価 FROM テーブルA WHERE 年月='2011/02/01') t1,
(SELECT 商品コード,単価 FROM テーブルA WHERE 年月='2011/01/01') t2
where t1.商品コード = t2.商品コード

または、
select t1.単価 as 当月,
(SELECT t2.単価 FROM テーブルA t2 WHERE t2.年月='2011/01/01' and t1.商品コード = t2.商品コード) as 前月,
t1.単価 - (SELECT t2.単価 FROM テーブルA t2 WHERE t2.年月='2011/01/01' and t1.商品コード = t2.商品コード) as 差分
from テーブルA t1 WHERE t1.年月='2011/02/01'

とすればよいだけですし。

お礼

ご回答ありがとうございます。

教えて頂いたSQLを元に考察してみます。
解決次第、ご連絡させて頂きます。