Register the time a machine running in a timerange using sql machinename startdatetime             enddatetime               timerunning(mins)   timerange
vxx:2019    2020-02-07 13:45:18.790    2020-02-07 16:00:38.920  135            12:00


the above machine runs from 13:45 to 16:00. I want to  register the first 15 mins in 12:00 timerange and next 120 mins in 2:00.

Currently my case statement is based on startdatetime and registers the hours in one timerange even though it overlaps two timeranges or can run across multiple timeranges. Also, the 24 hour is supposed to be broken down into 2 hour intervals. So total 12 intervals. And a process can run across multiple intervals.Also fyi i dont have create access on this database. Any help is appreciated. Thanks!

I am looking to present the data as

machinename startdatetime             enddatetime               timerunning(mins)   timerange
vxx:2019    2020-02-07 13:45:18.790    2020-02-07 16:00:38.920  15             12:00
vxx:2019    2020-02-07 13:45:18.790    2020-02-07 16:00:38.920  120            2:00


This is what i tried and it seems to be working.

--split duration on a 2 hr basis

select case when st &lt; startdatetime then startdatetime else st end [startdatetime],    case when et &gt; [enddatetime] then [enddatetime] else et end [enddatetime] from (select *,
            xstart = dateadd(mi, 120*(datediff(mi, 0, d.startdatetime)/120), 0),
            blocks = datediff(mi, d.[startdatetime], d.[enddatetime])/120+2
     from table d) d cross apply (    select
          st = dateadd(mi,v.number*120,xstart),
          et = dateadd(mi,v.number*120+120,xstart)    from master..spt_values v    where v.type='P' and v.number &lt;= d.blocks
     and d.[enddatetime] &gt; dateadd(mi,v.number*120,xstart)) v
The following code will spray your one row into 2 as desired.

Declare @starttime time
Declare @endtime time
Declare @machinename varchar(10)
Set @machinename = 'vxx:2019'
If OBJECT_ID ('tempdb..#A') is not null Drop Table #A
Create Table #A (machinename varchar(15), startdatetime datetime, enddatetime datetime, timerunning int, timerange varchar(10))
Insert into #A (machinename, startdatetime, enddatetime, timerunning, timerange)
Select 'vxx:2019',    '2020-02-07 13:45:18.790' ,   '2020-02-07 16:00:38.920' , 135, '12:00'

Select @starttime = cast(startdatetime as time)
from #A
where machinename = @machinename

Select @endtime = cast(enddatetime as time)
from #A
where machinename = @machinename

If (@starttime  between '12:00' and '14:00') and (@endtime &gt; '14:00')

Begin
Select machinename, startdatetime, enddatetime, datediff(minute,@starttime,'14:00') as timerunning, '12:00' as timerange
from #A
UNION ALL
Select machinename, startdatetime, enddatetime, datediff(minute,'14:00',@endtime), '2:00' as timerange
From #A
End



The two time ranges specified in the question are 12-2 and 2 - end time. It uses variables to determine whether the job runs across the time range or not. If the job runs across the time range then it just uses the datediff function to determine the time running in those timeranges and sprays it out into two rows. If you need to do this on a whole table a while loop is probably your best bet.