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 [予約名]

回答の条件
  • 1人10回まで
  • 13歳以上
  • 登録:2017/01/07 00:28:04
  • 終了:2017/01/08 09:38:15
id:pochi07

■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 [予約名]


としても計算できましたが、他に良い方法はないでしょうか。
プロシージャを使わない方法があれば良いですが、プロシージャで記述が必須でしょうか?

また、プロシージャでループした場合の予約名(グループ)の日付ごとのカウント方法がわからなくて困ってます。

ご教授お願いします。

ベストアンサー

id:degucho No.1

degucho回答回数254ベストアンサー獲得回数682017/01/07 23:57:52

これでいかがでしょうか

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を使用して作成したカレンダーを利用しています

id:pochi07

再帰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 [予約名]

2017/01/08 09:54:43

その他の回答(0件)

id:pochi07

質問文を編集しました。詳細はこちら

id:degucho No.1

degucho回答回数254ベストアンサー獲得回数682017/01/07 23:57:52ここでベストアンサー

これでいかがでしょうか

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を使用して作成したカレンダーを利用しています

id:pochi07

再帰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 [予約名]

2017/01/08 09:54:43
  • id:degucho
    あげられている予約情報では
    予約名ごとに重複はないようなので
    「すべての予約で10月の日付に範囲がかかっているものについて
    予約に含まれる日付の数を求める」という意味でしょうか?
    (今作成している重複ありも最終的にはすべて足して利用?)
    重複なし=1,2,4,5,16から31の計20日
    それとも実際は予約名ごとに日付の重複があるのでしょうか
  • id:pochi07
    分かりにくくてすみません

    予約情報のサンプルでは予約1に重複を入れてませんでしたが、実際には予約名ごとに重複が多々あります
    予約名ごとに予約を含む日数の数を求めたいです
    重複除外無し、有り両方とも利用します


    質問内容の訂正と、補足事項を追加致しました。

    ご教授お願いします

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません