Question

Provide sql query to calculate average time between when an order is made and when it is shipped. Use Avg() function to calculate average time.

Answer 1

Hi,

Please find the below details to find our the Average Time

--Declare Table
DECLARE @FirstTable TABLE (
                                                        ID INT, 
                                                        OrderDate datetime, 
                                                        OrderShippedDate Datetime
                                                        )

--declare date variables 
DECLARE @ToDate as datetime,@FromDate as datetime

--set variales values
 set @ToDate  = (SELECT CAST(CONVERT(VARCHAR(10), dateadd(day,-5, cast(getdate() as datetime)), 110) + ' 23:59:59' AS DATETIME))
 set @FromDate  = (SELECT CAST(CONVERT(VARCHAR(10), dateadd(day,-5, cast(getdate() as datetime)), 110) + ' 13:59:59' AS DATETIME))
 --Insert values into table
Insert Into @FirstTable values(1,  @FromDate, @ToDate)

--set variales values
 set @ToDate  = (SELECT CAST(CONVERT(VARCHAR(10), dateadd(day,-1, cast(getdate() as datetime)), 110) + ' 23:59:59' AS DATETIME))
 set @FromDate  = (SELECT CAST(CONVERT(VARCHAR(10), dateadd(day,-2, cast(getdate() as datetime)), 110) + ' 13:59:59' AS DATETIME))
  --Insert values into table
Insert Into @FirstTable values(2,  @FromDate, @ToDate)

--Print Table Detils
select * from @FirstTable

--FInd Average time
select ID ,AVG(DATEDIFF(day, OrderDate, OrderShippedDate)) as avg_day,
           AVG(DATEDIFF(minute, OrderDate, OrderShippedDate)) as avg_minute,
                   AVG(DATEDIFF(SECOND, OrderDate, OrderShippedDate)) as avg_sec
from @FirstTable
Group by ID

Output:

Hi,

It looks like you have fetched the data from 2 different tables. Accordigly. I ahve re-created the same way but we cannot find out the Average using AVG function on the basis of Date, It should be - Day, Hours, Minutes

--Declare Table
DECLARE @CustomerOrder TABLE (
                                                        CustomerOrderID INT, 
                                                        OrderDate datetime                                                      
                                                        )

--Declare Table
DECLARE @Shipment TABLE (
                                                        CustomerOrderID INT,                                                    
                                                        ShipmentDate Datetime
                                                        )

--declare date variables 
DECLARE @OrderDate as datetime,@OrderShippedDate as datetime

--set variales values
 set @OrderDate  = (SELECT CAST(CONVERT(VARCHAR(10), dateadd(day,-10, cast(getdate() as datetime)), 110) + ' 23:59:59' AS DATETIME))
 set @OrderShippedDate  = (SELECT CAST(CONVERT(VARCHAR(10), dateadd(day,-5, cast(getdate() as datetime)), 110) + ' 13:59:59' AS DATETIME))
 --Insert values into table
Insert Into @CustomerOrder values(1,  @OrderDate)
Insert Into @Shipment values(1,  @OrderShippedDate)
--set variales values
 set @OrderDate = (SELECT CAST(CONVERT(VARCHAR(10), dateadd(day,-3, cast(getdate() as datetime)), 110) + ' 23:59:59' AS DATETIME))
 set @OrderShippedDate  = (SELECT CAST(CONVERT(VARCHAR(10), dateadd(day,-2, cast(getdate() as datetime)), 110) + ' 13:59:59' AS DATETIME))
  --Insert values into table
Insert Into @CustomerOrder values(2,  @OrderDate)
Insert Into @Shipment values(2,  @OrderShippedDate)
--Print Table Detils
select * from @CustomerOrder
select * from @Shipment
--FInd Average time

select CO.CustomerOrderID,avg(datediff(day, CO.OrderDate, S.ShipmentDate))  'AvgTime_day' ,
avg(datediff(HOUR, CO.OrderDate, S.ShipmentDate))  'AvgTime_Hours' 
                from @CustomerOrder CO, @Shipment S 
where CO.CustomerOrderID = S.CustomerOrderID
Group By CO.CustomerOrderID 

Output of that Querry is :