Is There An Efficient Way To Break A Date Range Into Hours Per Day?

In SQL Server I am attempting to break a date range into hours per day and have the following bit of code which is OK for a short time frame, but rather inefficient for longer peri

Solution 1:

The best way would be to use recursive cte :

DECLARE @StartDate datetime = '2015-01-27 07:32:35.000',
        @EndDate   datetime = '2015-04-29 14:39:35.000';

WITH cte AS ( 
SELECT CAST(@StartDate AS DATE) startdate,DATEDIFF(minute, @StartDate, DATEADD(DAY, 1, CAST(@StartDate AS DATE) ) ) / 60.0 hours 
UNION ALL 
SELECT DATEADD(DAY,1, startdate), DATEDIFF(minute, DATEADD(DAY,1, startdate), CASE WHEN DATEADD(DAY,2, startdate) > @EndDate
    THEN @enddate ELSE DATEADD(DAY,2, startdate) END) / 60.0
FROM cte 
WHERE startdate <> CAST(@EndDate AS DATE) 
)

SELECT * FROM cte 

db<>fiddle here