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 [予約名]
■SQL(重複除外の場合)
SELECT [予約名],
CASE WHEN SUM(CASE WHEN [予約開始日] <= '2016/10/1'
AND '2016/10/1' <= [予約終了日] THEN 1 ELSE 0 END) > 0
THEN 1 ELSE 0 END
+ CASE WHEN SUM(CASE WHEN [予約開始日] <= '2016/10/2'
AND '2016/10/2' <= [予約終了日] THEN 1 ELSE 0 END) > 0
THEN 1 ELSE 0 END
+ ... (※残り 10/3~10/31 までの分)
AS '重複除外の予約日数合計'
FROM [予約情報]
GROUP BY [予約名]
としても計算できましたが、他に良い方法はないでしょうか。
プロシージャを使わない方法があれば良いですが、プロシージャで記述が必須でしょうか?
また、プロシージャでループした場合の予約名(グループ)の日付ごとのカウント方法がわからなくて困ってます。
ご教授お願いします。
これでいかがでしょうか
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を使用して作成したカレンダーを利用しています
これでいかがでしょうか
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を使用して作成したカレンダーを利用しています
再帰CTEというのがあるんですね、初めて知りました。
ありがとうございます。
ネットのサイトではなかなか見つけれない情報でしたのでとても助かりました。
GROUP化しての月単位集計が簡単に出力するのは
プロシージャを使用しなくてもできるんですね。
応用すれば、月単位以外に、週単位、日単位、時間単位でも集計ができそうです。
CTEのWITHでカンマ区切りだったので、
カレンダーと、予約が入っている日一覧の2つのテーブルを作成しているような
イメージでしょうか。
ご教授頂いたSQL文を元に、実際に動くかSQL ServerにてTESTし、
希望通りの出力結果を得られました。ありがとうございます。
/* 日程期間 予約重複有り日数(総予約日数)、予約重複無し日数(ここでは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 [予約名]
再帰CTEというのがあるんですね、初めて知りました。
2017/01/08 09:54:43ありがとうございます。
ネットのサイトではなかなか見つけれない情報でしたのでとても助かりました。
GROUP化しての月単位集計が簡単に出力するのは
プロシージャを使用しなくてもできるんですね。
応用すれば、月単位以外に、週単位、日単位、時間単位でも集計ができそうです。
CTEのWITHでカンマ区切りだったので、
カレンダーと、予約が入っている日一覧の2つのテーブルを作成しているような
イメージでしょうか。
ご教授頂いたSQL文を元に、実際に動くかSQL ServerにてTESTし、
希望通りの出力結果を得られました。ありがとうございます。