question

I need a table

Ordername Orderplaced Ordercompleted A 00:12:56 01:24:42 B 01:15:20 01:45:30 I need the result in this form :- Ordertime count(orders) 00:00 0 00:01 0 ' ' 00:12 0 00:13 1 00:14 1 ' ' 01:16 2 01:17 2 ' 01:25 1 01:26 1 ' 01:46 0 '
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
What have you tried so far?
0 Likes 0 ·

·
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;
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
Yes, that's correct. But I create the time dimension in a CTE instead of materializing it in a table. Ideally, one would create an inline table valued function of the CTE generating time slots, so the solution can become somewhat more flexible (change interval from one minute to five minutes, change start and end time etc).
0 Likes 0 ·
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
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
reformatted code
0 Likes 0 ·