Ufdjrw

Starting data:

Desired results something like this:

So it calculated the number of hours until the end of StartDateTime, if the EndDateTime is greater than end of day for StartDateTime. Then for every full day in between, it calculates 24 hours (this could stretch numerous days). And then when it gets to the EndDateTime - it calculates time from midnight (morning) to EndDateTime

I'm reading that I will probably need to use a recursive CTE, but I don't have any experience with recursions and am struggling.

this might get tricky, but I guess it can be solved using so called number table - i.e. table which has only one column populated with number sequence. In our case 0 based sequence.

The trick here is to get the number of days between start and end datetime. This value used in join between the data table and the numbers table will create the needed extra rows for each per day interval.

Of course we also have to setup properly start and end datetime of each day interval (CASE terms in the CTE)

Then we get for each per day interval number of minutes and divide by 60 to get proper decimal value.

Hope this helps.

Lets see the code:

-- input data
DECLARE @v_Dates TABLE
(
 id varchar(20),
 StartDateTime SMALLDATETIME,
 EndDateTime SMALLDATETIME
)

INSERT INTO @v_Dates (id, StartDateTime, EndDateTime)
VALUES ('example 1', '02-17-2019 0:45', '02-19-19 12:30'),
('example 2', '02-21-2019 18:00', '02-22-19 12:15'),
('example 3', '02-22-2019 20:15', '02-22-19 20:30');


-- so called Number table which holds numbers 0 - 9999 in this case 
DECLARE @v_Numbers TABLE
(
 Number INT
);

-- populating the number table
INSERT INTO @v_Numbers
SELECT TOP 10000 ROW_NUMBER() OVER(ORDER by t1.number) - 1 as Number
FROM master..spt_values t1 
CROSS JOIN master..spt_values t2

-- we parse the dates into the per day intervals
;WITH IntervalsParsed(id, StartDateTime, EndDateTime, Number, IntervalStartDateTime, IntervalEndDateTime) AS
(
SELECT id
,StartDateTime
,EndDateTime
,Number
, InervalStartDateTime = CASE
 WHEN D.StartDateTime > DATEADD(day, DATEDIFF(day, 0, D.StartDateTime), N.Number) THEN D.StartDateTime
 ELSE DATEADD(day, DATEDIFF(day, 0, D.StartDateTime), N.Number)
 END
, IntervalEndDateTime = CASE 
 WHEN D.EndDateTime < DATEADD(day, DATEDIFF(day, 0, D.StartDateTime), N.Number + 1) THEN D.EndDateTime
 ELSE DATEADD(day, DATEDIFF(day, 0, D.StartDateTime), N.Number + 1)
 END

FROM @v_Dates D
--this join basically creates the needed number of rows 
INNER JOIN @v_Numbers N ON DATEDIFF(day, D.StartDateTime, D.EndDateTime) + 1 > N.Number
)
-- final select
SELECT id
, StartDateTime
, EndDateTime
, IntervalStartDateTime
, IntervalEndDateTime
, Number
, DecimalValue = CAST( DATEDIFF(minute, IntervalStartDateTime, IntervalEndDateTime) AS DECIMAL)/60

FROM IntervalsParsed
ORDER BY id, Number

Just another option is an ad-hoc tally table in concert with a CROSS APPLY

Example

Select A.[column1]
 ,A.[StartDateTime]
 ,A.[EndDateTime]
 ,Hours = sum(1) / 60.0
 From @YourTable A
 Cross Apply (
 Select Top (DateDiff(MINUTE,[StartDateTime],[EndDateTime])+1) 
 D=DateAdd(MINUTE,-1+Row_Number() Over (Order By (Select Null)),[StartDateTime]) 
 From master..spt_values n1,master..spt_values n2
 ) B
 Group By [column1],[StartDateTime],[EndDateTime],cast(D as Date)

Returns

This may be little complicated, but here is one way to use recursive cte to get the output. You can add the start date with one day as long as it is less than end date of your column. Also declared a Static value to make sure we can get difference of 24 hours.

--Create a table

Select 'example1' exm, '2019-02-17 00:45:00' startdate, '2019-02-19 12:30:00' Enddate into #temp union all 

Select 'example2' exm, '2019-02-21 18:00:00' startdate, '2019-02-22 12:15:00' Enddate union all 
Select 'example3' exm, '2019-02-22 20:15:00' startdate, '2019-02-22 20:30:00' Enddate 

 Declare @datevalue time = '23:59:59' 

 ;with cte as (select exm, startdate, enddate, case when datediff(day, startdate, enddate) = 0 then datediff(SECOND, startdate, enddate) 
 when datediff(day, startdate, enddate)>0 then 
 datediff(SECOND, cast(startdate as time), @datevalue) 
 end as Hoursn, cast(dateadd(day, 1,cast(startdate as date)) as smalldatetime) valueforhours from #temp 
 union all 
 select exm, startdate, enddate, case when datediff(day, valueforhours, enddate) = 0 then datediff(SECOND, valueforhours, enddate) 
 when datediff(day, valueforhours, enddate)>0 then datediff(SECOND, cast(valueforhours as time), @datevalue) end as Hoursn, case when datediff(day,valueforhours, enddate) > 0 then dateadd(day,1,valueforhours) end as valueforhours 
 from cte 
 where 
 valueforhours <= cast(enddate as date)
 ) 
 select exm, startdate, Enddate, round(Hoursn*1.0/3600,2) as [hours] from cte 
 order by exm

Output:

 exm startdate Enddate hours
example1 2019-02-17 00:45:00 2019-02-19 12:30:00 23.250000
example1 2019-02-17 00:45:00 2019-02-19 12:30:00 24.000000
example1 2019-02-17 00:45:00 2019-02-19 12:30:00 12.500000
example2 2019-02-21 18:00:00 2019-02-22 12:15:00 6.000000
example2 2019-02-21 18:00:00 2019-02-22 12:15:00 12.250000
example3 2019-02-22 20:15:00 2019-02-22 20:30:00 0.250000