CODEWITHSUNDEEP
sqlsql-server
patel.milanb
patel.milanb

Reputation: 5992

grouping results based on time diff in sql

I have results like this 

TimeDiffMin | OrdersCount
10          |  2
12          |  5
09          |  6
20          | 15
27          | 11

I would like the following 

TimeDiffMin | OrdersCount
05          |  0
10          |  8
15          |  5
20          | 15
25          |  0
30          | 11

So you can see that i want the grouping of every 5 minutes and show the total order count in those 5 minutes. eg. 0-5 minutes 0 orders, 5-10 minutes 8 orders

any help would be appreciated.

current query:

 SELECT TimeDifferenceInMinutes, count(OrderId) NumberOfOrders  FROM (
 SELECT AO.OrderID, AO.OrderDate, AON.CreatedDate AS CancelledDate,     DATEDIFF(minute, AO.OrderDate, AON.CreatedDate) AS TimeDifferenceInMinutes 
FROM 
(SELECT OrderID, OrderDate FROM AC_Orders) AO
JOIN
(SELECT OrderID, CreatedDate FROM AC_OrderNotes WHERE Comment LIKE '%has been cancelled.') AON 
ON AO.OrderID = AON.OrderID
 WHERE DATEDIFF(minute, AO.OrderDate, AON.CreatedDate) <= 100  AND AO.OrderDate >= '2016-12-01'
  ) AS Temp1
 GROUP BY TimeDifferenceInMinutes

Upvotes: 0

Views: 88

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81990

Now, if you are open to a TVF.

I use this UDF to create dynamic Date/Time Ranges. You supply the range and increment

Declare @YourTable table  (TimeDiffMin int,OrdersCount int)
Insert Into @YourTable values
(10, 2),
(12, 5),
(09, 6),
(20,15),
(27,11)


Select TimeDiffMin = cast(R2 as int)
      ,OrdersCount = isnull(sum(OrdersCount),0)
From (Select R1=RetVal,R2=RetVal+5 From [dbo].[udf-Range-Number](0,25,5)) A
Left Join (
            -- Your Complicated Query
            Select * From @YourTable
          ) B on TimeDiffMin >= R1 and TimeDiffMin<R2
 Group By R1,R2
 Order By 1

Returns

TimeDiffMin OrdersCount
5           0
10          6
15          7
20          0
25          15
30          11

The UDF if interested

CREATE FUNCTION [dbo].[udf-Range-Number] (@R1 money,@R2 money,@Incr money)
Returns Table
Return (
    with cte0(M) As (Select cast((@R2-@R1)/@Incr as int)),
         cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N) As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a,cte1 b,cte1 c,cte1 d,cte1 e,cte1 f,cte1 g,cte1 h )

    Select RetSeq=1,RetVal=@R1 Union All Select N+1,(N*@Incr)+@R1
    From   cte2   
)
-- Max 100 million observations 
-- Select * from [dbo].[udf-Range-Number](0,4,0.25)

Upvotes: 2

iamdave
iamdave

Reputation: 12243

You can do this using a derived table to first build up your time difference windows and then joining from that to sum up all the Orders that fall within that window.

declare @t table(TimeDiffMin int
                ,OrdersCount int
                );
insert into @t values
 (10, 2)
,(12, 5)
,(09, 6)
,(20,15)
,(27,11);

declare @Increment int = 5;    -- Set your desired time windows here.

with n(n)
as
(  -- Select 10 rows to start with:
  select n from(values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as n(n)
),n2 as
(  -- CROSS APPLY these 10 rows to get 10*10=100 rows we can use to generate incrementing ROW_NUMBERs.  Use more CROSS APPLYs to get more rows:
  select (row_number() over (order by (select 1))-1) * @Increment as StartMin
        ,(row_number() over (order by (select 1))) * @Increment as EndMin
  from n                -- 10 rows
    cross apply n n2    -- 100 rows
    --cross apply n n3  -- 1000 rows
    --cross apply n n4  -- 10000 rows
)
select m.EndMin as TimeDiffMin
      ,isnull(sum(t.OrdersCount),0) as OrdersCount
from n2 as m
  left join @t t
    on(t.TimeDiffMin >= m.StartMin
       and t.TimeDiffMin < m.EndMin
       )
where m.EndMin <= 30    --  Filter as required
group by m.EndMin
order by m.EndMin

Query result:

TimeDiffMin OrdersCount
5           0
10          6
15          7
20          0
25          15
30          11

Upvotes: 1

Related Questions