受注から出荷までのリードタイムを集計するSQLの書き方

質問

データベースに受注テーブルがあり、
その項目として受注番号, 受注日, 出荷日があるとします。

＜受注テーブル＞
受注番号, 受注日, 出荷日
001, 2009/1/1, 2009/1/1
002, 2009/1/1, 2009/1/3
003, 2009/1/2, 2009/1/2
004, 2009/1/2, 2009/1/2
005, 2009/1/4,
...

この受注テーブルを元に、受注から出荷までにかかる作業効率
（リードタイム）を見る表をMS-SQLで作りたいと思っています。
抽出条件としては受注月指定をして受注日を対象にします。

＜出荷リードタイム＞
受注日, 注文件数, 当日出荷, 翌日出荷, 翌々日以降出荷, 未出荷
2009/1/1, 2, 1, 0, 1, 0
2009/1/2, 2, 2, 0, 0, 0
2009/1/3, 0, 0, 0, 0, 0
2009/1/4, 1, 0, 0, 0, 1

SUM(CASE WHEN (DATEDIFF(DAY,[受注日], [出荷日]) = 0) THEN 1 END) AS [当日出荷]
を利用して受注日をGroup Byすることで何となくは出来たのですが、
注文のない日（上の場合は2009/1/3）もリストとして出したいので、
もう一工夫必要なのかと思っています。

お知恵を拝借できたら幸いです。

回答No.2

jamshid6 · ベストアンサー

テーブルは増やしたくなくても、ストアドプロシージャを使う気はあるのですね？ならば、今回のケースでは日付テーブルに相当するものをテーブル関数で作成してはどうですか。
ストアドにする場合もロジック自体は同様なものを組み込むだけですが、日付部分だけを関数化すれば同じようなものを複数作成したいときに流用できます。

CREATE FUNCTION GETDATETBL(@YYYYMM varchar(7))
RETURNS @DATETBL TABLE ([DATE][datetime])
AS
BEGIN
DECLARE @DATE datetime
IF ISDATE(@YYYYMM+'/01')=0 RETURN
SET @DATE=CONVERT(datetime,@YYYYMM+'/01')
DECLARE @WDATE datetime
SET @WDATE=@DATE
WHILE (MONTH(@WDATE)=MONTH(@DATE))
BEGIN
INSERT INTO @DATETBL VALUES (@WDATE)
SET @WDATE=DATEADD(d,1,@WDATE)
END
RETURN
END

これで指定月のすべての日を返すテーブル関数ができあがりますから、
SELECT * FROM GETDATETBL('2009/01')
という使い方でテーブル同様に使うことが可能です。

お礼

アイデアありがとうございます。
上記にいただいたアドバイスを自分なりにWEB検索で調査を進めたところ、回答番号：No.1の方のアイデアの日付テーブルをテーブル変数を使えば良さそうだという答えに行き着き、回答番号：No.1の方のアイデアといただいたアイデアを組み合わせて望むものが作成できました。

DECLARE @DATE datetime
DECLARE @WDATE datetime
DECLARE @DAYS TABLE ([受注日] [smalldatetime] NULL)

SET @WDATE=@DATE　

WHILE (MONTH(@WDATE)=MONTH(@DATE))
BEGIN
INSERT INTO @DAYS(DATE_CREAT) VALUES (@WDATE)
SET @WDATE=DATEADD(d,1,@WDATE)
END

これで日付テーブルが変数として得られましたので
この後、@DAYSテーブルと受注テーブルをLEFT JOINします。

回答No.1

noah7150

SQLではないものを出力する事が出来ません。
なので日付連番のテーブルを用意して

＜TBL_DAYS＞
受注日
2009/1/1
2009/1/2
2009/1/3
2009/1/4

このテーブルと外部結合すればよい

select T1.受注日,
IsNull(T2.注文件数,0) AS 注文件数,
IsNull(T2.当日出荷,0) AS 当日出荷,
...
from TBL_DAYS AS T1
left join(
select 受注日,
Count(*) AS 注文件数,
SUM(CASE WHEN (DATEDIFF(DAY,受注日,出荷日)=0) THEN 1 END) AS 当日出荷,
...
) AS T2 on T1.受注日 = T2.受注日

お礼

日付テーブルのアイデアありがとうございます。
実現できそうですね。

ただ、できれば、テーブルを追加しない方向で考えたいと思っています。
ストアドプロシージャを活用したらできるでしょうか？

補足

テーブルを追加しないで、テーブル変数を使うことで実現させました。アドバイスありがとうございました。