いつもお世話になっております。
当方、とある顧客データのピックアップ作業をSQL Server 2012 で行っているのですが、
業務についてまだ日が浅く、なかなか思うようにデータ抽出が行えておりません。
ご協力お願いいたします。
・取得したいデータ
当日から7日間の連日日付において、3つの構造が同じテーブルから、条件の一致した[年月日][倉庫コード][製品コード]でグループにした本数の合算を求める。その際、
を都度合算した結果のデータ。
(例えば、最新年月日が20150903の場合、取得したいデータは
20150903 → 20150901から20150902までのSUM値(本数) 20150902 → 20150901のみ(本数) 20150901 → 20150801から20150831までのSUM値(本数) 20150831 → 20150801から20150830までのSUM値(本数) 20150830 → 20150801から20150829までのSUM値(本数) 20150829 → 20150801から20150828までのSUM値(本数) 20150828 → 20150801から20150827までのSUM値(本数)
取得データにつきましては、以下のような形で取得したいと考えております。
[年月日],[倉庫コード],[製品コード親],SUM[(本数)]
20150903,AAAAA,abuodsds,156325
20150903,BBBBB,dsfgrhgf,59826
20150902,AAAAA,dfefgrth,258847
このように、選択年月日-1日の年月日の当月前日までのデータの合算を1回のSQL実行で行いたいと考えております。
年月日の割当については、一時テーブルで最新日7日分を選択し、結合できるようですが…(実は検索等で調べたので信憑性があやしいです、スイマセン)
取得データにつきましては、以下のような形で取得したいと考えております。
[年月日],[倉庫コード],[製品コード],SUM[(本数)]
20150903,AAAAA,abuodsds,156325
20150903,BBBBB,dsfgrhgf,59826
20150902,AAAAA,dfefgrth,258847
・・・・
・取得方法(チャレンジしたところまで)
・データ登録日時テーブル[AA10]と[AA11]から最新年月日を取得し、その年月日を基準に、
最新年月日に対する月末日、前月初日の年月日を取得
(テーブル[AA10]と[AA11]は、データ登録日時確認用のテーブルです。構成カラムは[年月日],[ファイル名称]で、[AA10]が[Aテーブル]と[Bテーブル]の更新情報、[AA11]が[Cテーブル]の更新情報となります。業務上、「当日」の年月日はこのテーブルから取得しないといけません。)
・データ登録日時テーブル[AA10]と[AA11]の最新日時7日分の日時範囲で一時テーブルを作成
・本数データ取得対象の[Aテーブル]と[Bテーブル]と[Cテーブル]をUNION ALLする
ここから、どうすればいいのかわからなくなっております。
(一応下記SQLでOVER句を使用しておりますが、意味は通っておりません。)
一時テーブルの使用・OVER句(PARTITION BY~)による[年月日][倉庫コード][製品コード]のグループ化などを行う必要があるそうですが、イマイチ理解できておりません。
大変わかりくにい質問となってしまいましたが、ご協力お願いいたします。
--以下、SQL文(書いたことろまで)
DECLARE
@START_DATE AS NUMERIC --開始日(前月月初日)
, @END_DATE AS NUMERIC --実績終了日(前日)
, @LAST_EOM AS NUMERIC --前月末日
SET @END_DATE = (SELECT MAX(T10.[年月日])
FROM(
SELECT MAX(T01.[年月日]) AS [年月日] FROM [BATABLE].[dbo].[AA10] T01 WHERE T01.[品種] = 'AT' AND T01.[内訳] NOT IN ('6', '9')
UNION ALL
SELECT MAX(T02.[年月日]) AS [年月日] FROM [BATABLE].[dbo].[AA11] T02 WHERE T02.[品種] = 'AT' AND T02.[内訳] NOT IN ('6', '9')
) T10);
SET @LAST_EOM=(SELECT CAST(CONVERT(VARCHAR,EOMONTH(DATEADD(MONTH,-1,CONVERT(DATETIME,CAST(@END_DATE AS VARCHAR),112))),112) AS NUMERIC));
SET @START_DATE=(SELECT CAST(CONCAT(LEFT(@LAST_EOM,6),'01') AS NUMERIC));
WITH
/*********************************************************
日付範囲
**********************************************************/
[NT_日付範囲] AS
(SELECT TOP 7 [年月日] FROM
(SELECT TOP 7 [年月日] FROM [BATABLE].[dbo].[AA10] GROUP BY [年月日] WHERE [年月日] ORDER BY [年月日] DESC
UNION
SELECT TOP 7 [年月日] FROM [BATABLE].[dbo].[AA11] GROUP BY [年月日] WHERE [年月日] ORDER BY [年月日] DESC
) X
ORDER BY [年月日] DESC
)
SELECT
[年月日]
, [倉庫コード]
, [製品コード]
, SUM([本数]) AS [本数]
FROM
(SELECT
T0.[年月日]
, T0.[出荷先コード] AS [倉庫コード]
, T0.[製品コード]
, --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [本数]
, --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [本数]
FROM
[BATABLE].[dbo].[Aテーブル] T0
WHERE
T0.[年月日] BETWEEN @START_DATE AND @END_DATE
GROUP BY
T0.[年月日]
, T0.[出荷先コード]
, T0.[製品コード]
UNION ALL SELECT T0.[年月日] , T0.[出荷先コード] AS [倉庫コード] , T0.[製品コード] , --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [本数] , --SUM(T0.[出荷本数]) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [本数] FROM [BATABLE].[dbo].[Bテーブル] T0 WHERE T0.[年月日] BETWEEN @START_DATE AND @END_DATE GROUP BY T0.[年月日] , T0.[出荷先コード] , T0.[製品コード] UNION ALL SELECT T0.[年月日] , T0.[出荷先コード] AS [倉庫コード] , T0.[製品コード] , --SUM(T0.[入荷本数]*-1) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [本数] , --SUM(T0.[入荷本数]*-1) OVER(PARTITION BY LEFT([年月日],6),[出荷先コード],[製品コード親] ORDER BY [年月日] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [本数] FROM [BATABLE].[dbo].[Cテーブル] T0 WHERE T0.[年月日] BETWEEN @START_DATE AND @END_DATE GROUP BY T0.[年月日] , T0.[入荷先コード] , T0.[製品コード] )
GROUP BY
[年月日]
, [倉庫コード]
, [製品コード]