时间戳

首页 > 解决方案 > 如何从sql中的特定日期获取一个季度的日期列表？

sql-server - 如何从sql中的特定日期获取一个季度的日期列表？

问题描述

我想找出提供日期所在季度的所有日期（在这种特殊情况下，我试图搜索从第一次销售的季度开始的所有销售）。

我最初的尝试是从提供的日期中提取月份和年份，并在查找记录时将其用作条件，不幸的是我不知道如何提供具有多个条件的 where 子句。

DECLARE @FirstSale DateTime = (SELECT TOP 1 [Sale].[DateCreated] FROM [Sale] ORDER BY [Sale].[DateCreated] ASC)
DECLARE @FirstSaleMonth int = Month(@FirstSale)
DECLARE @FirstSaleYear int = Year(@FirstSale)

SELECT [Sale].[DateCreated] FROM [Sale]
WHERE Month([Sale].[DateCreated]) =  
CASE 
    WHEN @FirstSaleMonth = 1 OR @FirstSaleMonth = 2 OR @FirstSaleMonth = 3
        THEN 1 OR 2 OR 3    
    WHEN @FirstSaleMonth = 4 OR @FirstSaleMonth = 5 OR @FirstSaleMonth = 6
        Then 4 OR 5 OR 6
    WHEN @FirstSaleMonth = 7 OR @FirstSaleMonth = 8 OR @FirstSaleMonth = 9
        Then 7 OR 8 OR 9
    WHEN @FirstSaleMonth = 10 OR @FirstSaleMonth = 11 OR @FirstSaleMonth = 12
        Then 10 OR 11 OR 12
END
AND Year([Sale].[DateCreated]) = @FirstSaleYear

所以我最终做的只是重复条件不同的结果，最终会达到预期的条件

WHERE Month([Sale].[DateCreated]) =  
CASE 
    WHEN @FirstSaleMonth = 1 OR @FirstSaleMonth = 2 OR @FirstSaleMonth = 3
        THEN 1
    WHEN @FirstSaleMonth = 4 OR @FirstSaleMonth = 5 OR @FirstSaleMonth = 6
        Then 4
    WHEN @FirstSaleMonth = 7 OR @FirstSaleMonth = 8 OR @FirstSaleMonth = 9
        Then 7
    WHEN @FirstSaleMonth = 10 OR @FirstSaleMonth = 11 OR @FirstSaleMonth = 12
        Then 10
END
AND Year([Sale].[DateCreated]) = @FirstSaleYear

OR

Month([Sale].[DateCreated]) = 
CASE 
    WHEN @FirstSaleMonth = 1 OR @FirstSaleMonth = 2 OR @FirstSaleMonth = 3
        THEN 2
    WHEN @FirstSaleMonth = 4 OR @FirstSaleMonth = 5 OR @FirstSaleMonth = 6
        Then 5
    WHEN @FirstSaleMonth = 7 OR @FirstSaleMonth = 8 OR @FirstSaleMonth = 9
        Then 8
    WHEN @FirstSaleMonth = 10 OR @FirstSaleMonth = 11 OR @FirstSaleMonth = 12
        Then 11
END
AND Year([Sale].[DateCreated]) = @FirstSaleYear

OR

Month([Sale].[DateCreated]) = 
CASE 
    WHEN @FirstSaleMonth = 1 OR @FirstSaleMonth = 2 OR @FirstSaleMonth = 3
        THEN 3
    WHEN @FirstSaleMonth = 4 OR @FirstSaleMonth = 5 OR @FirstSaleMonth = 6
        Then 6
    WHEN @FirstSaleMonth = 7 OR @FirstSaleMonth = 8 OR @FirstSaleMonth = 9
        Then 9
    WHEN @FirstSaleMonth = 10 OR @FirstSaleMonth = 11 OR @FirstSaleMonth = 12
        Then 12
END
AND Year([Sale].[DateCreated]) = @FirstSaleYear

但我觉得它太长了，而且我已经多次使用相同的条件。我还在学习，我想知道是否有更有效的方法可以在将来应用它？

标签： sql-server

解决方案

那么你应该使用宿舍。

DECLARE @FirstSale DateTime = (SELECT TOP 1 [Sale].[DateCreated] FROM [Sale] ORDER BY [Sale].[DateCreated] ASC)
DECLARE @FirstSaleQuarter int = DATEPART(QUARTER, @FirstSale)
DECLARE @FirstSaleYear int = Year(@FirstSale)

SELECT 
    [Sale].[DateCreated] 
FROM 
    [Sale]
WHERE 
    DATEPART(QUARTER, [Sale].[DateCreated]) = @FirstSaleQuarter AND 
    Year([Sale].[DateCreated]) = @FirstSaleYear

但是 SARGable （可以正确使用索引）的最佳选择是使用范围（避免将函数应用于表中的列WHERE）：

DECLARE @FirstSale DateTime = (SELECT TOP 1 [Sale].[DateCreated] FROM [Sale] ORDER BY [Sale].[DateCreated] ASC)

DECLARE @StartDate DATE = DATEFROMPARTS(
    Year(@FirstSale),
    (3 * (DATEPART(QUARTER, @FirstSale) - 1) + 1,
    1)

DECLARE @EndDate DATE = DATEADD(MONTH, 3, @StartDate)

SELECT 
    [Sale].[DateCreated] 
FROM 
    [Sale]
WHERE 
    [Sale].[DateCreated] >= @StartDate AND
    [Sale].[DateCreated] < @EndDate

推荐阅读