Find Customers with Multiple Orders on the Same Day SQL Server

Find Customers with Multiple Orders on the Same Day Query Using MS SQL Server?

To find customers who placed multiple orders on the same day, you can follow these steps:

1. Group orders by customer and order date.
2. Count the number of orders for each customer on each day.
3. Filter to include only those groups where the count is greater than one.

Assuming you have an Orders table with the following columns:

• OrderID
• CustomerID
• OrderDate

Here’s a SQL query to find customers with multiple orders on the same day:

SQL Query

WITH DailyOrderCounts AS (

    SELECT

        CustomerID,

        OrderDate,

        COUNT(*) AS OrderCount

    FROM Orders

    GROUP BY CustomerID, OrderDate

)

SELECT

    CustomerID,

    OrderDate

FROM DailyOrderCounts

WHERE OrderCount > 1

ORDER BY CustomerID, OrderDate;

Explanation:

1. CTE DailyOrderCounts:

   • COUNT(*) AS OrderCount: Counts the number of orders for each CustomerID on each OrderDate.
   • GROUP BY CustomerID, OrderDate: Groups the data by CustomerID and OrderDate to get the number of orders per customer per day.

2. Final Query:

   • WHERE OrderCount > 1: Filters the results to include only those customers who have placed more than one order on the same day.

Example Data and Results

Assume the Orders table has the following data:

OrderID	CustomerID	OrderDate
1	1001	2024-08-01
2	1001	2024-08-01
3	1001	2024-08-02
4	1002	2024-08-01
5	1002	2024-08-02
6	1003	2024-08-01
7	1003	2024-08-01

The DailyOrderCounts CTE would produce:

CustomerID	OrderDate	OrderCount
1001	2024-08-01	2
1001	2024-08-02	1
1002	2024-08-01	1
1002	2024-08-02	1
1003	2024-08-01	2

The final query result would be:

CustomerID	OrderDate
1001	2024-08-01
1003	2024-08-01

This result shows customers who placed multiple orders on the same day.

You can execute this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to find customers with multiple orders on the same day.