是否可以添加第二个where条件来选择相同但来自其他日期范围的数据?

您的预期输出与2个月的结果不匹配,但以下是实现您想要的结果的代码。你只需要添加一个 SUM(CASE...)

SELECT  
    d.DeptCode,
    d.DeptName,
    SUM(s.TotalCost) as YTD,
    SUM(CASE WHEN s.ShipmentDate >= DATEADD(month, -2, GETDATE()) then s.TotalCost else 0 END) as PTD

FROM [Departments] AS d
INNER JOIN Shipments AS s
ON d.DeptCode= s.DeptCode
WHERE Year(s.ShipmentDate) = Year(GETDATE())
GROUP BY d.DeptCode, d.DeptName

只需再添加一列,当不在两个月范围内时返回0,例如。 SUM(CASE WHEN (date check) THEN (amount) ELSE 0 END) . 查看第五行:

SELECT  
    d.DeptCode,
    d.DeptName,
    SUM(s.TotalCost) as YTD,
    SUM(CASE WHEN DateDiff(MONTH, s.ShipmentDate, GetDate()) < 2 THEN s.TotalCost ELSE 0 END) PTD,
FROM [Departments] AS d
INNER JOIN Shipments AS s
ON d.DeptCode= s.DeptCode
WHERE s.ShipmentDate BETWEEN DateAdd(yyyy, DateDiff(yyyy, 0, GetDate()), 0) 
AND GETDATE()
GROUP BY d.DeptCode, d.DeptName

试试这个:

nbr_last2month_ AS
(
SELECT DISTINCT 
   Sum(s.[TotalCost]) AS 'PTD', 
   s.DeptCode, 
   s.DeptName
 FROM    [Shipements] s
         LEFT JOIN [Departements] d ON d.[DeptCode] = s.[DeptCode] 
 WHERE   Year(date_) LIKE Year(GETDATE()) 
         AND MONTH(ShipementDate) LIKE Month(Getdate()) - 2
 Group by DeptCode
),

nbr_YTD_ AS
(
SELECT DISTINCT 
   Sum(s.[TotalCost]) AS 'YTD', 
   s.DeptCode, 
   s.DeptName
 FROM    [Shipements] s
         LEFT JOIN [Departements] d ON d.[DeptCode] = s.[DeptCode]
 WHERE    Year(ShipementDate) LIKE Year(GETDATE()) 
 Group by DeptCode
),

SELECT 
   A.DeptCode,
   A.DeptName,
   YTD,
   PTD
FROM   nbr_YTD_ A
       LEFT JOIN nbr_last2month_ B on B.DeptCode = A.DeptCode
ORDER BY DeptCode