SQLで日付を持ったデータの集計を行いたい

Question

SQL(SQL Server)で日付を持ったデータの集計をしています。
開始日時と終了日時を持ったデータに対し、1ヶ月分、23時59分59秒に、
開始されていて終了されていないデータの件数を一覧にしたいのですが
うまい方法が思いつきません。

データはごく簡単に書くと以下のようなテーブルです。

CREATE TABLE hoge (
    ID int PRIMARY KEY,
    開始日時 datetime2(0),
    終了日時 datetime2(0), --終了していない場合はNULL
    )

1日分を持ってくるのは以下のような感じで考えています。

DECLARE @対象日時 datetime2(0)
    SET @対象日時 = '2016-07-22 23:59:59'

SELECT COUNT(*) AS 数
  FROM hoge
 WHERE 開始日時 <= @対象日時
   AND (終了日時 < @対象日時 OR 終了日時 IS NULL)

日付マスタのようなものはあるので、そこで1ヶ月分の日付のリストを作成し、LEFT OUTER JOIN させるようなことを考えています。

ループさせるのではなくSQLで解決させるにはどうすればよいでしょうか?

どのような結果が欲しいか追記します。

たとえば、下記のようなレコードがあった場合

ID,開始日時,終了日時
1,"2016-04-02 01:00:00","2016-04-05 01:00:00"
2,"2016-04-04 01:00:00",NULL

結果としては

日付,件数
"2016-04-01", 0 //1件もない
"2016-04-02", 1 //1のデータのみ
"2016-04-03", 1 //1のデータのみ
"2016-04-04", 2 //1と2のデータ
"2016-04-05", 1 //1のデータは23:59:59には終了していて2のデータのみ
"2016-04-06", 1 //2のデータのみ

といった感じになります。日付のところは、上記の例だと

SELECT 日付 FROM 日付マスタ WHERE 日付 BETWEEN '2016-04-01' AND '2016-04-06'

というように取り出せます。

わかりにくくて申し訳ありませんがよろしくお願いいたします。

Comments

[dupont_kedama]

希望する結果セットの例を提示していただいた方が良いと思います。

Answer 1

期待する結果から考えるとこんな感じでしょうか?
1か月分が当月なのか前月なのか任意なのかがわからないので
とりあえず手入力で書いておきます

SELECT
	  T2.日付
	, COUNT(*) AS 数
FROM hoge T1
LEFT JOIN 日付マスタ T2
-- 日付マスタの日付が開始日時以降
ON T2.日付 >= CAST(T1.開始日時 AS DATE)
-- 日付マスタの日付が終了日時の0時0分より前 か 終了日時が設定されていない
AND (T2.日付 < CAST(T1.終了日時 AS DATE) OR T1.終了日時 IS NULL) 
AND T2.日付 BETWEEN '2016-04-01' AND '2016-04-06'
GROUP BY
	  T2.日付

Answer 2

こんばんは。
要望の出力を行うのでしたら最終的に下記のような形のSQLに持ち込めれば良さ気です。

SELECT
    T.日付
,   T.件数
FROM
    ( 
    SELECT
        M.日付
    ,   SUM( 
            CASE 
                WHEN H.開始日時 IS NOT NULL 
                THEN 1 
                ELSE 0 
            END
		) AS 件数
    FROM
        日付マスタ M 
        LEFT JOIN HOGE H 
            ON M.日付 BETWEEN H.開始日 AND COALESCE(H.終了日, 月末日) 
    GROUP BY
            M.日付 
    ) T
WHERE
    T.日付 BETWEEN 検索条件:開始日 AND 検索条件:終了日
ORDER BY
    T.日付

SQLの結合所条件の部分は、
終了日時NULLを考慮して月末日を入れる形を取ってますが、
この箇所は検索条件のTO条件と一致させても良いと思います。

一応月内の最終日まで表示することを考慮して月末日とは書いておりますが･･･
後、開始日、終了日の項目は時間部を切り捨てていることを前提として書いてますので悪しからず・・・

###追記
SQL内のSUM関数使っている所ですが、
列名指定のCOUNTはNULLはカウント対象外となるので、
A.Ichiさん掲示のコードのように**COUNT(開始日時)**で事足りますね。

Answer 3

とりあえず１ヶ月は手入力で

select cast(c.日付 as date) 日付, count(h.開始日時) 件数 
from 日付マスタ c left join hoge h on cast(c.日付 as date) >= cast(h.開始日時 as date) and 
(h.終了日時 is null or cast(c.日付 as date) <= h.終了日時) 
where c.日付 between '2016-04-01' and '2016-04-06'
group by 1

Answer 4

1か月分の終了してないデータをサブクエリで絞り込む。
この際、開始時刻は年月日の形式に変換しておく。
上記を from にし、年月日を group by して
あとは年月日ごとに count すればいける感じ!?

// ご希望の内容を勘違いしてたら申し訳ない。
--- 追加 ---

修正された質問を拝見しました。

テーブルと(必要な分の)日付マスタを cross join して
該当レコード(マスタ由来の日付が範囲内にある)を選別して
マスタ由来の日付で gropu by して count した結果と
日付マスタ(必要な分)を組み合わせればいけそうですが…

正直なところ、そこまで面倒なことをするくらいなら
すなおにループしますね。自分だったら。

Comments

[hos]

ありがとうございます。
ご回答いただいた方法だと開始時刻での集計になる感じだと思います。

もうすこしやりたいことを追記いたしました。

Answer 5

結局うまくいかず、1日ごとデータを取込みました。

Answer 6

結局うまくいかず、1日ごと処理を行うことにしました。

Answer 7

予めカレンダーテーブルを用意しておかないかぎり、
データの無い日付を抽出することはできません。
予め10年分くらいの有効日付を投入しておけばよいかと