CODEWITHSUNDEEP
sqlsql-server
skyman
skyman

Reputation: 2335

Query to total monthly hours for events spanning month borders

I have a series of events that are unique and may continue for an arbitrary time. I need to calculate how may hours the events contribute to a monthly totals, given that the event may commence before a specific month (in which case it will contribute to the previous month as well as the current month) and finish after a specific month.

The data looks something like:

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

Ultimately, I am try to generate a list grouped by month and year that totals the "Event Hours" for each month (not each event - events within the month are summed).

To be honest, I am not even sure where to start with this.

Upvotes: 1

Views: 201

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

The easy way is using a months table because you can have empty months.

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

Then you do a join with your table.

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

You have 4 cases

  • a event where begin and end are inside the month
  • a event where end is beyond the month end
  • a event start before and end after a month
  • a month with no events.

Upvotes: 1

MUHAMMAD TASADDUQ ALI
MUHAMMAD TASADDUQ ALI

Reputation: 387

Try. This will work.

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

Here is the SQLFiddle.

Upvotes: 0

Related Questions