CODEWITHSUNDEEP
sql-server
ocinisme
ocinisme

Reputation: 301

Getting Average from DATEDIFF Result from the same ID Number

this is my table currently.

enter image description here

Employees will have multiple order dates and shipped dates. What I want to get is the average of each employee's Diff sum.

Been struggling with how to achieve this.

This is what I have done so far.

USE Northwind 

SELECT 
    e.EmployeeID, 
    e.LastName, 
    o.OrderDate, 
    o.ShippedDate, 
    DATEDIFF(DAY, o.OrderDate, o.ShippedDate) as Diff 
FROM 
    Employees as e 
    JOIN Orders as o ON e.EmployeeID = o.EmployeeID

Upvotes: 1

Views: 2233

Answers (2)

Mitch Wheat
Mitch Wheat

Reputation: 300499

SELECT 
    e.EmployeeID, 
    AVG(CAST(DATEDIFF(DAY, o.OrderDate, o.ShippedDate) as float)) as AvgDiff 
FROM 
    Employees as e 
    JOIN Orders as o ON e.EmployeeID = o.EmployeeID
Group By e.EmployeeID

If you want more than the EmployeeId in the result set, join back like so:

select 
    e.EmployeeId
    e.LastName, 
    a.AvgDiff
From
    Employees as e 
Join     (SELECT 
              e.EmployeeID, 
              AVG(CAST(DATEDIFF(DAY, o.OrderDate, o.ShippedDate) as float)) as AvgDiff 
          FROM 
              Employees as e 
              JOIN Orders as o ON e.EmployeeID = o.EmployeeID
          Group By e.EmployeeID) a ON a.EmployeeId = e.EmployeeId

Upvotes: 4

bummi
bummi

Reputation: 27367

SELECT 
    e.EmployeeID, 
    e.LastName, 
    AVG(1.0 * DATEDIFF(DAY, o.OrderDate, o.ShippedDate)) as AVGDiff 
FROM 
    Employees as e 
    JOIN Orders as o ON e.EmployeeID = o.EmployeeID
Group by  e.EmployeeID, e.LastName
Order by  e.EmployeeID, e.LastName

Upvotes: 3

Related Questions