question

The below script is completely untested, but if it doesn't work, I think you can still get the idea from it. It uses a few CTEs to generate times from 12:00 to midnight and then left joins from that to the orders table to get the counts. with tens as ( select n from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(n) ), thousands as ( select row_number() over(order by t.n) -1 as rn from tens t cross join tens t2 cross join tens t3 cross join tens t4 cross join tens t5 ),minutes as ( select dateadd(minute,rn,cast('12:00' as time)) as the_time from thousands where dateadd(minute,rn,cast('12:00' as time)) = o.orderplaced and m.the_time < o.ordercompleted group by m.the_time order by m.the_time;

Here's another solution (basically the same as the above example) declare @orders table(Ordername varchar(54), Orderplaced datetime , Ordercompleted datetime) insert into @orders values ('A','2017-12-30 00:12:56', '2017-12-30 01:24:42') ,('B','2017-12-30 01:15:20', '2017-12-30 01:45:30') --minutes table declare @Minutes table([Minute] varchar(5)) declare @ToDay datetime = '2018-01-09 00:00:00' --loop to fill minutes table with all minutes in a day while cast(@ToDay as Date) <'2018-01-10' Begin insert into @Minutes(Minute) select left(cast( @ToDay as Time),5) set @ToDay = DATEADD(MINUTE,1, @ToDay) End --get number of active orders per minute select a.Minute, isnull(count(b.Ordername),0) as orders from @Minutes a left join @orders b on a.Minute between left(cast( b.Orderplaced as time),5) and left(cast( b.Ordercompleted as time),5) where a.Minute <= (select left(cast(getdate() as time),5)) group by a.Minute order by 1