MSSQL：如何将同一列两个日期范围的销售数据分两列返回

需求说明 #

从业务表中按收款中心维度，统计并并列展示两组数据：

• Total_Sale：本月1日至昨日的销售总额
• Total_Sale2：上月1日至与本月昨日对应日期的销售总额（例：今日6月20日，则统计5月1日-19日数据）

现有可运行代码（仅统计当月数据） #

SET @FDM = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

SELECT sum ([LAB_TRN_BillHeader].[AmountToBePaid]) as Total_Sale
          ,LAB.dbo.[LAB_TRN_BillHeader].[CollectingCenterCode]
          ,LAB.dbo.[LAB_Comm_MST_CollectingCenter].[Name]
          ,LAB.dbo.[LAB_Comm_MST_Branch].[BranchName]

FROM Lab.dbo.[LAB_TRN_BillHeader]
INNER JOIN LAB.dbo.[LAB_Comm_MST_CollectingCenter] 
  on LAB.dbo.[LAB_TRN_BillHeader].[CollectingCenterCode] = LAB.dbo.[LAB_Comm_MST_CollectingCenter].[CollectingCenterCode]
INNER JOIN LAB.dbo.[LAB_Comm_MST_Branch] 
  on LAB.dbo.[LAB_TRN_BillHeader].[BranchCode] = LAB.dbo.[LAB_Comm_MST_Branch].[BranchCode]

WHERE Date between @FDM and DATEADD(day,0, CAST(GETDATE() AS date)) 
  and {{select_Laboratory}} 
  and LAB.dbo.[LAB_TRN_BillHeader].[IsVoid] = '0' 
  and LAB.dbo.[LAB_TRN_BillHeader].[CollectingCenterCode] in ('URCR022','MRPMC','KUCC','KOCC','EHECC')

GROUP BY LAB.dbo.[LAB_TRN_BillHeader].[CollectingCenterCode], LAB.dbo.[LAB_Comm_MST_CollectingCenter].[Name], LAB.dbo.[LAB_Comm_MST_Branch].[BranchName]

修改后代码（支持同期对比） #

-- 定义日期变量：本月起始日、昨日日期
DECLARE @CurrentMonthStart DATE = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
DECLARE @Yesterday DATE = DATEADD(day, -1, CAST(GETDATE() AS DATE))
-- 上月同期的起始和结束日
DECLARE @LastMonthStart DATE = DATEADD(mm, -1, @CurrentMonthStart)
DECLARE @LastMonthEnd DATE = DATEADD(mm, -1, @Yesterday)

SELECT 
  -- 本月截至昨日销售额
  SUM(CASE WHEN CAST(LAB_TRN_BillHeader.CreateDate AS DATE) BETWEEN @CurrentMonthStart AND @Yesterday THEN LAB_TRN_BillHeader.AmountToBePaid ELSE 0 END) AS Total_Sale,
  -- 上月同期销售额
  SUM(CASE WHEN CAST(LAB_TRN_BillHeader.CreateDate AS DATE) BETWEEN @LastMonthStart AND @LastMonthEnd THEN LAB_TRN_BillHeader.AmountToBePaid ELSE 0 END) AS Total_Sale2,
  LAB_TRN_BillHeader.CollectingCenterCode,
  LAB_Comm_MST_CollectingCenter.Name,
  LAB_Comm_MST_Branch.BranchName
FROM Lab.dbo.LAB_TRN_BillHeader
INNER JOIN LAB.dbo.LAB_Comm_MST_CollectingCenter 
  ON LAB_TRN_BillHeader.CollectingCenterCode = LAB_Comm_MST_CollectingCenter.CollectingCenterCode
INNER JOIN LAB.dbo.LAB_Comm_MST_Branch 
  ON LAB_TRN_BillHeader.BranchCode = LAB_Comm_MST_Branch.BranchCode
-- 筛选日期范围包含本月和上月同期，减少数据扫描
WHERE CAST(LAB_TRN_BillHeader.CreateDate AS DATE) BETWEEN @LastMonthStart AND @Yesterday
  AND {{select_Laboratory}} -- Metabase实验室筛选器（实际为LAB_TRN_BillHeader.BranchCode = '001'类条件）
  AND LAB_TRN_BillHeader.IsVoid = '0'
  AND LAB_TRN_BillHeader.CollectingCenterCode IN ('URCR022','MRPMC','KUCC','KOCC','EHECC')
GROUP BY 
  LAB_TRN_BillHeader.CollectingCenterCode,
  LAB_Comm_MST_CollectingCenter.Name,
  LAB_Comm_MST_Branch.BranchName

期望查询结果 #

代码说明 #

1. 日期变量：明确计算本月/上月的起始、结束日期，逻辑清晰且易于维护
2. 条件聚合：通过SUM(CASE WHEN ...)在同一分组中分别统计两个日期范围的销售额，实现并列展示
3. 优化WHERE条件：仅筛选需要的日期区间（上月同期至昨日），减少不必要的数据扫描
4. 保留原有业务筛选：包含作废标记、收款中心白名单、Metabase实验室筛选器逻辑

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