代码之家 › 专栏 › 技术社区 › dmbb

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

where-clause select tsql sql-server sql

dmbb · 技术社区 · 8 年前

我在SQL Server中有两个表。

我想选择DeptCode、DeptName、YearToDate、PeriodToDate(例如2个月),并按DeptCode对其进行分组。我想得到一个结果:

在YTD列中,我想得到自01/01/actualYear以来的总成本之和。
在PTD列中,我想得到过去两个月的总数。

我创建了一段代码,显示了正确的YTD成本,但我不知道如何添加下一个代码来获得其他日期范围的总成本。可以这样做吗?

SELECT  
    d.DeptCode,
    d.DeptName,
    SUM(s.TotalCost) as YTD
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

3 回复 | 直到 8 年前

steve v 8 年前

您的预期输出与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

John Wu 8 年前

只需再添加一列,当不在两个月范围内时返回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

Mohamed Azizi 8 年前

试试这个:

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

推荐文章