Split Hours into Multiple Months using SQL

Question

CREATE TABLE EventLog 
(
  EventID INT
, EventName VARCHAR(50) NOT NULL
, EventStartDateTime DATETIME NOT NULL
, EventEndDateTime   DATETIME NULL 
)


INSERT INTO EventLog(EventID, EventName, EventStartDateTime, EventEndDateTime)
VALUES(100, 'Planting', '20210620 10:34:09 AM','20211018 10:54:49 PM')
 ,(200, 'Foundation', '20200420 10:34:09 AM','20211018 10:54:49 PM')
 ,(300, 'Seeding', '20210410 10:27:19 AM','')
 ,(400, 'Spreading', '20220310 10:24:09 PM','')

I have a requirement to split hours into multiple months and even years depending on the length of the event. In some cases, the event may have an end date but if the event is still ongoing, there will be no end date.

The result or output of the solution is to be held by another table:

CREATE TABLE EventSummary
(  
  EventID INT
, EventName VARCHAR(50) NOT NULL
, [Year] INT
, [MonthName] VARCHAR(25) 
, [Hours] DECIMAL(12,2) 
)

The image above is the output of the first row.

If the event runs over multiple years, the values should be spread across the multiple years and months likewise. In cases where there is no end date I am to use GETUTCDATE() to do the calculation. Some events span across months or event years. I would like to be able to break down the total duration into individual month's duration (or individual duration by month) in hours respectively and populate it into a table

Consider that I have an event with start and end date: '20210620 10:34:09 AM','20211018 10:54:49 PM' For the first month which basically is not a full month, I am to calculate the remaining hours of that month and store it against that month. I do the same for the next month. If the event runs for the entire month which is now the month of July, I store the entire hours for that month which is 744 hours against July. I keep doing that till the end of the event. But if the event is still open(blank or empty) I use GETUTCDATE() as the end date The sum of Hours is grouped by EventID, EventName, Year and Months It is expected that the first and or last month may be decimals as they may not be fully formed.

I have tried to work this out but do not know how to get the best result with SQL Server. I kindly will appreciate your help with this. Thanks

Answer 1

If I understand correctly, you can try to use CTE recursive, get each month startdate then use antoher cte2 get the starttime and endtime between each month.

;WITH CTE AS (
   SELECT EventID,EventName,EventStartDateTime,IIF(EventEndDateTime = '',GETUTCDATE(),EventEndDateTime) EventEndDateTime
   FROM EventLog
   UNION ALL
   SELECT EventID,EventName, DATEADD(month, DATEDIFF(month, 0, DATEADD(month , 1 , EventStartDateTime)), 0) , EventEndDateTime
   FROM CTE 
   WHERE  DATEADD(month, DATEDIFF(month, 0, DATEADD(month , 1 , EventStartDateTime)), 0)  <= EventEndDateTime
), CTE2 AS (
   SELECT EventID,EventName,EventStartDateTime,LEAD(EventStartDateTime,1,EventEndDateTime) OVER(PARTITION BY EventID,EventName ORDER BY EventStartDateTime) n_EventStartDateTime
   FROM CTE
)
INSERT INTO EventSummary(EventID,EventName,Year,MonthName,Hours)
SELECT EventID,EventName,YEAR(EventStartDateTime),DATENAME(MONTH,EventStartDateTime),DATEDIFF(second, EventStartDateTime, n_EventStartDateTime) / 3600.0
FROM CTE2
option (maxrecursion 0)

sqlfiddle