x

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

        '
       







         










more ▼

asked Jan 09 at 11:22 AM in Default

avatar image

blackwidow
11 1 4

What have you tried so far?

Jan 09 at 12:08 PM JohnM
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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)) < ='23:59'
 )
 select m.the_time, count(*) from
 minutes m
 left join thetablewithorders o
 on m.the_time >= o.orderplaced and m.the_time < o.ordercompleted
 group by m.the_time

order by m.the_time;

more ▼

answered Jan 09 at 01:17 PM

avatar image

Magnus Ahlkvist
22.1k 20 43 42

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).

Jan 10 at 09:43 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jan 09 at 01:33 PM

avatar image

tzvikl
31 3

reformatted code

Jan 09 at 02:00 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2198
x1171
x121
x60

asked: Jan 09 at 11:22 AM

Seen: 37 times

Last Updated: Jan 10 at 09:43 AM

Copyright 2018 Redgate Software. Privacy Policy