月毎、日別に条件に当てはまるレコードを抽出したい。

質問

visual web developer で貸し出し表を作成しています。
sqlサーバーの予定テーブルに下記のようなデータが入っています。

ID　　貸し出し日　　返却予定日　　貸し出した物
1　　　2010/03/31　　2010/04/03　　本
2　　　2010/04/02　　2010/04/02　　DVD
3　　　2010/04/02　　2010/04/03　　CD

上記のデータで4月に貸し出しを行っている毎日の状況を抽出するにはどのようなsql文を書けばよろしいでしょうか。
下記のような結果を期待しています。

日付　　　　　　ID　　　　貸出日　　　返却予定日　　　貸し出した物
2010/04/01　　1　　　　2010/03/31　2010/04/03　　　本
2010/04/02　　1　　　　2010/03/31　2010/04/03　　　本
2010/04/02　　2　　　　2010/04/02　2010/04/02　　　DVD
2010/04/03　　1　　　　2010/03/31　2010/04/03　　　本
2010/04/03　　3　　　　2010/04/02　2010/04/03　　　CD
2010/04/04
2010/04/05
2010/04/06
2010/04/07
.....
2010/04/30

予定テーブルに対して「貸出日が2010/04/30以下」 or 「返却予定日が2010/04/01以上」のwhere条件で
4月の予定を抽出する事は出来ますが、複数貸し出しがある日を複数行で返し、無い日についてもNULLを返すやり方が思いつきません。
どのようなsql文を書けばよろしいでしょうか。
宜しくお願いいたします。

回答No.2

1050 円（@1050YEN） · ベストアンサー

データ量にもよるとは思いますが、、、
・貸し出し表テーブルの件数が膨大ではない
・集計は年月で絞っての日毎リスト
程度であれば、一時テーブルでもよいと思います。

一時テーブルの利点としては、カレンダテーブルのような物が必要ないということです。
欠点は、内部のループでカレンダを自力で作成するため、集計スパンが広いと、ＤＢに負荷がかかります。

カレンダテーブルの利点としては、集計の範囲が広くてもループ処理を必要なく、集計が行えます。
欠点は、カレンダがマスタとなるので、メンテナンスを求められます。

どちらがよいかは、システムの業務フローや状態から判断するものだと思うので、ご自身でお考えください。

ストアドサンプルを載せます。
見やすくするために、[全角スペース]を利用しています。
[全角スペース]を[半角スペース2個に置換]してお使いください。

---------------------------------------
↓プロシージャ呼出はこれ（2010年04月）
EXEC PRC_月別貸出リスト '201004'
---------------------------------------

↓プロシージャ(初回はクリエイト)
CREATE PROCEDURE PRC_月別貸出リスト
--ALTER PROCEDURE PRC_月別貸出リスト
(
　@s年月 AS VARCHAR(6)
)
AS
BEGIN
　-- 結果返却用の一時テーブル定義
　CREATE TABLE #TMP
　(
　　日付　　　　　DATETIME,
　　ID　　　　　　INT,
　　貸出日　　　　DATETIME,
　　返却予定日　　DATETIME,
　　貸し出した物　VARCHAR(50)
　);
　DECLARE @d対象日　DATETIME;
　DECLARE @i日　　　INT;

　-- 引数年月の初日を設定
　SET @d対象日　= @s年月　+ '01';
　-- 一回目のループを通すために、0日とする
　SET @i日　= 0;

　-- 翌月までループする
　WHILE @i日 < DAY(@d対象日)
　BEGIN
　　-- 抽出結果を一時テーブルに格納していく
　　INSERT INTO #TMP
　　SELECT
　　　　@d対象日,
　　　　T.ID,
　　　　T.貸し出し日,
　　　　T.返却予定日,
　　　　T.貸し出した物
　　FROM
　　　　貸し出し表　T
　　WHERE
　　　　@d対象日 BETWEEN T.貸し出し日 AND T.返却予定日;

　　-- 処理を行った日付を記憶
　　SET @i日　　　= DAY(@d対象日);
　　-- 次のループのために、対象日をインクリメント
　　SET @d対象日　= DATEADD(DAY, 1, @d対象日);
　END;

　-- 抽出結果のアウトプット
　SELECT * FROM #TMP
　ORDER BY 日付, ID
END

お礼

ご回答ありがとうございました。
一時テーブルの作成もストアドプロシージャも敷居が高いなっというイメージがあってなんとなく通り過ぎていた部分でした。
仰る通りで、メンテナンスやレスポンスを考えて一時テーブルやカレンダーテーブルの選択を考えたいと思います。ありがとうございました。

回答No.3

1050 円（@1050YEN）

#2です。

失礼しました。
#1さんのって、カレンダのマスタを利用しているタイプだと勘違いしていました。

「CROSS APPLY hoge.nodes」

これよいですね。
勉強になりました。
m(_ _)m

回答No.1

jamshid6

SQLでは本質的に１件しかないものを２件以上にすることも、0件のものを1件にすることもできません。

従って、このようなニーズがある場合は、
a.2010/04/01から2010/04/30までの日付が入った「日付テーブル」なるものを別途用意しておく
b.同様のものを処理の先頭でワークテーブルやテーブル変数として生成し、それを使う
　（当然１クエリにはなりませんので、ストアドプロシージャやテーブル変数として実装するのが普通）
などの対応が一般的です。

私もとりあえずはaの方法をお勧めします。その場合は「日付」というテーブルを用意しておいて、
以下のクエリとなります。

SELECT
d.日付,
t.貸し出し日,
t.返却予定日,
t.貸し出した物
FROM 日付 d
LEFT OUTER JOIN 貸し出し表 t ON d.日付 BETWEEN t.貸し出し日 AND t.返却予定日
WHERE d.日付 BETWEEN '2010/04/01' AND '2010/04/30'
ORDER BY d.日付,t.ID

どうしても日付テーブルを別途作れないし、１クエリで実行したいという状況であれば、
SQL Server 2005以降であれば、以下の方法が可能です。
ただし、この中身を理解するのは、難しいかもしれません（XQueryを使用しているため）。

SELECT
d.日付,
t.ID,
t.貸し出し日,
t.返却予定日,
t.貸し出した物
FROM
(SELECT
DATEADD(d,x.c.value('.','int'),DATEADD(d,-1,'2010/04/01')) 日付
FROM
(SELECT
CONVERT(xml,'<D>'+REPLACE('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31',
',','</D><D>')+'</D>') 日付XML
) a
CROSS APPLY 日付XML.nodes('/D') AS x(c)
) d
LEFT OUTER JOIN 貸し出し表 t ON d.日付 BETWEEN t.貸し出し日 AND t.返却予定日
WHERE d.日付 BETWEEN '2010/04/01' AND '2010/04/30'
ORDER BY d.日付,t.ID

お礼

回答ありがとうございました。
そうですよね。やはり日付テーブルを用意してという事になりますよね。
ご提案いただいたaの方法をベースに考えてみます。
実はaの方法の場合にユーザー側で見たい月を選択して表示する方法がよくわからず質問にいたりました。
XQueryは今回初めて知りました。よく調べてみます。
難易度高そうですね。ありがとうございました！