Find the Average Delivery Time by Product Category Query Using MS SQL Server?

To find the average delivery time by product category using Microsoft SQL Server, you'll need to:

1. Calculate the delivery time for each order by finding the difference between the delivery date and the order date.
2. Aggregate the average delivery time for each product category.

Assuming you have the following tables:

• Orders table with columns: OrderID, OrderDate, DeliveryDate.
• OrderItems table with columns: OrderID, ProductID, Quantity.
• Products table with columns: ProductID, CategoryID.
• Categories table with columns: CategoryID, CategoryName.

Here's a step-by-step SQL query to calculate the average delivery time by product category:

SQL Query

-- Find the average delivery time by product category

WITH DeliveryTimes AS (

    SELECT

        oi.ProductID,

        DATEDIFF(DAY, o.OrderDate, o.DeliveryDate) AS DeliveryTimeDays

    FROM

        Orders o

        JOIN OrderItems oi ON o.OrderID = oi.OrderID

    WHERE

        o.DeliveryDate IS NOT NULL AND o.OrderDate IS NOT NULL

),

ProductCategories AS (

    SELECT

        p.ProductID,

        c.CategoryID

    FROM

        Products p

        JOIN Categories c ON p.CategoryID = c.CategoryID

),

CategoryDeliveryTimes AS (

    SELECT

        pc.CategoryID,

        AVG(dt.DeliveryTimeDays) AS AverageDeliveryTime

    FROM

        DeliveryTimes dt

        JOIN ProductCategories pc ON dt.ProductID = pc.ProductID

    GROUP BY

        pc.CategoryID

)

SELECT

    c.CategoryName,

    cdt.AverageDeliveryTime

FROM

    CategoryDeliveryTimes cdt

    JOIN Categories c ON cdt.CategoryID = c.CategoryID

ORDER BY

    cdt.AverageDeliveryTime;

Explanation

1. DeliveryTimes CTE:

   • DATEDIFF(DAY, o.OrderDate, o.DeliveryDate) AS DeliveryTimeDays: Computes the delivery time in days for each order.
   • JOIN OrderItems oi ON o.OrderID = oi.OrderID: Joins with OrderItems to get product information for each order.
   • WHERE o.DeliveryDate IS NOT NULL AND o.OrderDate IS NOT NULL: Ensures that both delivery and order dates are present to calculate the delivery time.

2. ProductCategories CTE:

   • JOIN Categories c ON p.CategoryID = c.CategoryID: Joins Products with Categories to get the category for each product.

3. CategoryDeliveryTimes CTE:

   • AVG(dt.DeliveryTimeDays) AS AverageDeliveryTime: Calculates the average delivery time for each category.
   • GROUP BY pc.CategoryID: Groups the results by CategoryID to compute the average delivery time per category.

4. Main Query:

   • JOIN Categories c ON cdt.CategoryID = c.CategoryID: Joins with the Categories table to get the category names.
   • ORDER BY cdt.AverageDeliveryTime: Orders the results by average delivery time for better readability.

Additional Considerations

• Handling Missing Data: Ensure that OrderDate and DeliveryDate are properly populated. Handle cases where data might be missing or incorrect.
• Indexes: Indexes on OrderDate, DeliveryDate, and ProductID can help improve query performance.
• Data Quality: Verify that the Orders, OrderItems, Products, and Categories tables contain accurate and complete data.

Adjust column names and table structures according to your actual schema. This query calculates the average delivery time by product category effectively.