人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

SQL Server 2012 のSQL文について

10月を集計月として、予約名をグループ化して、
重複除外無しの予約日数合計と、
重複除外有りの予約日数合計を求めたいのですが、

重複除外無しは求めることができたのですが、重複除外有りについてわからなく困っています。

どなたかわかる方いらっしゃいましたら、ご教授をお願いします。

■予約情報 (※訂正してます。)
予約名, 予約開始日, 予約終了日
予約1, 2016/9/2, 2016/10/5
予約1, 2016/10/4, 2016/10/30
予約2, 2016/10/1, 2016/10/5
予約3, 2016/10/16, 2016/11/6

■結果
予約1, 30
予約2, 5
予約3, 16

■SQL(重複除外無しの場合) (※WHERE条件削除)
SELECT [予約名],
SUM(DATEDIFF(d,
CASE WHEN [予約開始日] BETWEEN '2016/10/1' AND '2016/10/31' THEN [予約開始日]
WHEN [予約開始日] < '2016/10/1' AND [予約終了日] >= '2016/10/1' THEN '2016/10/1' END,
CASE WHEN [予約終了日] BETWEEN '2016/10/1' AND '2016/10/31' THEN [予約終了日]
WHEN [予約終了日] > '2016/10/31' AND [予約開始日] <= '2016/10/31' THEN '2016/10/31' END
) + 1
) AS '重複除外無しの予約日数合計'
FROM [予約情報]
GROUP BY [予約名]

●質問者: pochi07
●カテゴリ:コンピュータ
○ 状態 :終了
└ 回答数 : 1/1件

▽最新の回答へ

質問者から

説明の分かりにくい部分があり申し訳ございませんが、

重複除外有りは、
10月に予約していた日数の合計なので、複数予約していたとしても、最大日数は31日になります。


1 ● degucho
ベストアンサー

これでいかがでしょうか

WITH
カレンダー(日) AS (
SELECT CONVERT(DATETIME,'2016-10-01') 
UNION ALL
SELECT DATEADD(D,1,日)
 FROM カレンダー
 WHERE 日 < CONVERT(DATETIME,'2016-10-31')
),
予約が入っている日一覧 AS (
SELECT L.予約名
 ,R.日
 FROM 予約情報 L,カレンダー R
 WHERE R.日 BETWEEN L.予約開始日 AND L.予約終了日
)
SELECT 予約名
 ,COUNT( 日) 重複ありの予約日数合計
 ,COUNT(DISTINCT 日) 重複除外の予約日数合計
 FROM [予約が入っている日一覧]
GROUP BY 予約名
ORDER BY 予約名

再帰CTEを使用して作成したカレンダーを利用しています


pochi07さんのコメント
再帰CTEというのがあるんですね、初めて知りました。 ありがとうございます。 ネットのサイトではなかなか見つけれない情報でしたのでとても助かりました。 GROUP化しての月単位集計が簡単に出力するのは プロシージャを使用しなくてもできるんですね。 応用すれば、月単位以外に、週単位、日単位、時間単位でも集計ができそうです。 CTEのWITHでカンマ区切りだったので、 カレンダーと、予約が入っている日一覧の2つのテーブルを作成しているような イメージでしょうか。 ご教授頂いたSQL文を元に、実際に動くかSQL ServerにてTESTし、 希望通りの出力結果を得られました。ありがとうございます。 >|sql| /* 日程期間 予約重複有り日数(総予約日数)、予約重複無し日数(ここでは10月の予約日数) 日程期間の日付はプログラム側で渡す。 param: StartDate = '2016-10-01', EndDate = '2016-10-31' */ /* -- cte_name ( column_name [,...n] ) -- */ WITH カレンダー(日) AS ( /* -- CTE_query_definition -- Anchor member is defined. */ SELECT CONVERT(DATETIME, '2016-10-01') UNION ALL /* -- CTE_query_definition -- Recursive member is defined referencing cte_name. */ SELECT DATEADD(D, 1, 日) FROM [カレンダー] WHERE [日] < CONVERT(DATETIME, '2016-10-31') ), [予約が入っている日一覧] AS ( SELECT L.[予約名], R.[日] FROM [TEST_DB].[dbo].[予約情報] L, [カレンダー] R WHERE R.[日] BETWEEN L.[予約開始日] AND L.[予約終了日] ) /* -- Statement using the CTE -- */ SELECT [予約名], COUNT([日]) AS '予約重複有り日数(総予約日数)', COUNT(DISTINCT [日]) AS '予約重複無し日数(予約日数)', DATEDIFF(d, '2016/10/1', '2016/10/31') + 1 AS '期間日数(ここでは月の日数)', FROM [予約が入っている日一覧] GROUP BY [予約名] ORDER BY [予約名] ||<
関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ