Find Customers with Orders in Both the First and Last Quarters of the Year

Find Customers with Orders in Both the First and Last Quarters of the Year Query Using MS SQL Server?

To find customers with orders in both the first and last quarters of the year, you can use a combination of GROUP BY, HAVING, and conditional logic to ensure the orders are in the specified quarters.

Here's a step-by-step approach to constructing the SQL query in Microsoft SQL Server:

1. Identify the Tables and Columns

Assume you have the following tables:

• Orders table with columns CustomerID, OrderID, and OrderDate.

2. Write the Query

You'll need to:

1. Determine which orders fall into the first and last quarters of the year.
2. Use GROUP BY and HAVING to filter customers with orders in both quarters.

Here’s a sample SQL query to achieve this:

WITH QuarterlyOrders AS (

    SELECT

        CustomerID,

        DATEPART(QUARTER, OrderDate) AS OrderQuarter

    FROM

        Orders

    WHERE

        DATEPART(YEAR, OrderDate) = YEAR(GETDATE())  -- Use the current year or specify a year

    GROUP BY

        CustomerID,

        DATEPART(QUARTER, OrderDate)

)

SELECT

    CustomerID

FROM

    QuarterlyOrders

GROUP BY

    CustomerID

HAVING

    COUNT(CASE WHEN OrderQuarter = 1 THEN 1 END) > 0 AND

    COUNT(CASE WHEN OrderQuarter = 4 THEN 1 END) > 0;

Explanation:

• QuarterlyOrders CTE: This Common Table Expression (CTE) calculates the quarter for each order and filters by the current year. Replace YEAR(GETDATE()) with a specific year if needed.

  • DATEPART(QUARTER, OrderDate) AS OrderQuarter: Extracts the quarter number from the OrderDate.

• Main Query:

  • GROUP BY CustomerID: Groups results by customer.
  • HAVING Clause: Filters customers to ensure that they have orders in both the first and last quarters of the year.
    • COUNT(CASE WHEN OrderQuarter = 1 THEN 1 END) > 0: Ensures there is at least one order in the first quarter.
    • COUNT(CASE WHEN OrderQuarter = 4 THEN 1 END) > 0: Ensures there is at least one order in the last quarter.

This query will give you a list of CustomerIDs who have placed orders in both the first and last quarters of the current year. Adjust the year or additional conditions as needed for your specific requirements.