SQL优化：如何高效获取指定日期所在季度的销售日期？

Hey there! Let's fix this problem of fetching all sale dates from the same quarter as your very first sale record. Your initial attempts ran into syntax issues (like trying to return multiple values in a CASE statement) or ended up being way too repetitive, so here are two cleaner, more efficient approaches tailored for SQL Server:

Approach 1: Compare Year and Quarter Directly #

This method uses built-in date functions to grab the year and quarter of your first sale, then filters the full dataset to match those values. It’s straightforward and easy to read:

-- Capture the year and quarter of the first sale
DECLARE @FirstSaleYear INT, @FirstSaleQuarter INT;
SELECT TOP 1 
    @FirstSaleYear = YEAR(DateCreated),
    @FirstSaleQuarter = DATEPART(QUARTER, DateCreated)
FROM Sale
ORDER BY DateCreated ASC;

-- Get all sales from the same year and quarter
SELECT DateCreated
FROM Sale
WHERE 
    YEAR(DateCreated) = @FirstSaleYear
    AND DATEPART(QUARTER, DateCreated) = @FirstSaleQuarter;

Approach 2: Use Date Range Queries (Better for Performance) #

If your DateCreated column has an index (which it should for date filtering!), this method is more efficient because it avoids applying functions directly to the column (which can prevent index usage). Instead, we calculate the start of the target quarter and the start of the next quarter, then use a range filter:

DECLARE @FirstSale DATE = (SELECT TOP 1 DateCreated FROM Sale ORDER BY DateCreated ASC);

-- Calculate the first day of the target quarter
DECLARE @QuarterStart DATE = DATEFROMPARTS(
    YEAR(@FirstSale),
    ((DATEPART(QUARTER, @FirstSale) - 1) * 3) + 1,
    1
);

-- Calculate the first day of the next quarter (used as the upper bound, exclusive)
DECLARE @NextQuarterStart DATE = DATEADD(QUARTER, 1, @QuarterStart);

-- Fetch all sales within the target quarter
SELECT DateCreated
FROM Sale
WHERE 
    DateCreated >= @QuarterStart
    AND DateCreated < @NextQuarterStart;

Why These Work Better Than Your Original Attempts #

• Your first try had invalid CASE syntax: CASE statements can only return a single value, so THEN 1 OR 2 OR 3 won’t work.
• Your second approach repeated the same year/quarter logic three times, making the code hard to maintain and more prone to typos.
• Both methods above are concise, readable, and the second one is optimized for speed when dealing with large datasets.

内容的提问来源于stack exchange，提问作者Noah Mendez