CODEWITHSUNDEEP
sqlsql-server
ayman
ayman

Reputation: 29

Getting average difference between two dates in minutes

I am trying to get avg receiving time in minutes for each outlet.

I have tow tables Orders and ReceivedOrders.

Orders

OrderID OutletID OrderDate
1       1        2017-04-10 17:04:41.000

ReceivedOrders

ReceivingID OrderID ReceivingDate
1           1       2017-04-10 17:06:31.000

i have tried the below query but its reruns zero as avg receiving time

SQL Query

SELECT        Outlets.OutletName , avg(datediff(MM, Orders.OrderDate, ReceivedOrders.ReceivingDate)) as Receive
FROM          dbo.Orders INNER JOIN
              dbo.Outlets ON dbo.Orders.OutletID = dbo.Outlets.OutletID INNER JOIN
              dbo.ReceivedOrders ON dbo.Orders.OrderID = dbo.ReceivedOrders.OrderID 
group by dbo.Outlets.OutletName

Output

OutletName Receive
Outlet1    0

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Your code is doing exactly what you are specifying. You are getting the date diff in months, not minutes.

When using date parts, just spell out the full name of the date part:

SELECT ol.OutletName,
       avg(datediff(minute, o.OrderDate, ro.ReceivingDate)) as Receive
FROM dbo.Orders o INNER JOIN
     dbo.Outlets ol
     ON o.OutletID = ol.OutletID INNER JOIN
     dbo.ReceivedOrders ro
     ONo.OrderID = ro.OrderID 
GROUP BY ol.OutletName;

The above counts minute boundaries between two values. You may want parts of minutes, in which case I would use a smaller unit. Milliseconds are definitely an option, but they can overflow pretty easily if the dates are even a few months apart. So, you might really want something more like this:

SELECT ol.OutletName,
       avg(datediff(second, o.OrderDate, ro.ReceivingDate)/60.0) as minutesToReceive
FROM dbo.Orders o INNER JOIN
     dbo.Outlets ol
     ON o.OutletID = ol.OutletID INNER JOIN
     dbo.ReceivedOrders ro
     ONo.OrderID = ro.OrderID 
GROUP BY ol.OutletName;

Note the use of 60.0 rather than 60 to force non-integer arithmetic.

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Use datediff with millisecond option:

select Outlets.OutletName,
    avg(datediff(ms, Orders.OrderDate, ReceivedOrders.ReceivingDate)) / 60000 as Receive
from dbo.Orders
inner join dbo.Outlets on dbo.Orders.OutletID = dbo.Outlets.OutletID
inner join dbo.ReceivedOrders on dbo.Orders.OrderID = dbo.ReceivedOrders.OrderID
group by dbo.Outlets.OutletName

Upvotes: 1

Related Questions