查询跨越月份边界的事件的每月总小时数

Question

我有一系列唯一的事件，它们可以持续任意时间。我需要计算事件对每月总数贡献的小时数，因为事件可能在特定月份之前开始(在这种情况下，它将贡献前一个月和当前一个月)，并在特定月份之后结束。

数据看起来像:

Event | StartDate | EndDate   |
-------------------------------
| 1   | 10/01/2015| 11/01/2015|
| 2   | 20/12/2014| 9/01/2015 |
| 3   | 25/01/2015| 14/02/2015|

最终，我将尝试生成一个按月份和年份分组的列表，以汇总每个月的"事件小时数"(不是每个事件-一个月内的事件被求和)。

老实说，我甚至不知道从哪里开始。

Answer 1

简单的方法是使用月份表，因为您可以使用空月份。

create table months (
   month_id integer,
   date_ini datetime,
   date_end datetime
) 

然后对表执行join。

SQL Fiddle Demo

WITH ranges as (
    SELECT *
    FROM 
        months m 
        LEFT JOIN events e 
            on   e.StartDate <= m.d_end
            and  e.EndDate >= m.d_begin
 ) 
SELECT r.*, 
       DATEDIFF(hour, 
                CASE 
                    WHEN StartDate > d_begin THEN StartDate
                    WHEN StartDate IS NULL THEN NULL
                    ELSE d_begin
                END, 
                CASE 
                    WHEN EndDate < d_end THEN EndDate
                    WHEN EndDate IS NULL THEN NULL
                    ELSE DATEADD(day,1,d_end)
                END) as Hours
FROM ranges r

你有4个案例

• 开始和结束在月份内的事件
• 结束时间超过月末的事件
• 事件开始于一个月之前，结束于一个月之后
• 没有事件的一个月。

Answer 2

尝试一下。

WITH EventInfo AS 
(
   SELECT EVENT, DATEDIFF(HOUR, StartDate,EndDate) AS hours,
        DATEPART(MONTH, EndDate)  AS Month,
        DATEPART(Year, EndDate)  AS Year
    FROM events
)  
SELECT Month, Year, SUM(hours) AS Total_hours
FROM EventInfo
Group By Month, Year

这是SQLFiddle。