如何在仪表板展示多时段金额？SQL日期筛选查询问题求助

日期筛选写法的核心问题 #

你当前使用DATEPART提取月/年进行筛选的方式，存在以下潜在问题导致结果异常：

1. 时区不匹配：如果CreatedDate存储的是UTC时间，但GETDATE()返回服务器本地时间，会导致月/年匹配偏差；
2. 索引失效：函数包裹字段会导致CreatedDate上的索引无法被利用，不仅查询效率低，还可能因全表扫描的隐性逻辑问题导致结果错误；
3. 边界值误差：当CreatedDate包含毫秒级精度时（如2024-05-31 23:59:59.999），部分数据库会自动进位到下月，导致DATEPART筛选漏判。

各时间范围的正确查询写法 #

以下是本月、上月、近3个月、近6个月的高效且准确的SQL查询：

本月数据

SELECT SUM(RequestAmount)
FROM Transactions MT
INNER JOIN User_Heirarchy_Details UHD ON MT.UserID = UHD.UserID
WHERE UHD.ParentUserID IN (SELECT ParentUserID FROM User_Heirarchy_Details WHERE UserID = 38)
  -- 本月第一天0点到下月第一天0点的闭开区间
  AND MT.CreatedDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
  AND MT.CreatedDate < DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
  AND MT.[Status] IN ('SUCCESS', 'PENDING')

上月数据

SELECT SUM(RequestAmount)
FROM Transactions MT
INNER JOIN User_Heirarchy_Details UHD ON MT.UserID = UHD.UserID
WHERE UHD.ParentUserID IN (SELECT ParentUserID FROM User_Heirarchy_Details WHERE UserID = 38)
  -- 上月第一天0点到本月第一天0点的闭开区间
  AND MT.CreatedDate >= DATEFROMPARTS(YEAR(DATEADD(MONTH, -1, GETDATE())), MONTH(DATEADD(MONTH, -1, GETDATE())), 1)
  AND MT.CreatedDate < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
  AND MT.[Status] IN ('SUCCESS', 'PENDING')

近3个月（自然月，含当前月）

SELECT SUM(RequestAmount)
FROM Transactions MT
INNER JOIN User_Heirarchy_Details UHD ON MT.UserID = UHD.UserID
WHERE UHD.ParentUserID IN (SELECT ParentUserID FROM User_Heirarchy_Details WHERE UserID = 38)
  -- 往前推2个月的第一天到下月第一天（如5月则包含3、4、5月）
  AND MT.CreatedDate >= DATEADD(MONTH, -2, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
  AND MT.CreatedDate < DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
  AND MT.[Status] IN ('SUCCESS', 'PENDING')

近6个月（自然月，含当前月）

SELECT SUM(RequestAmount)
FROM Transactions MT
INNER JOIN User_Heirarchy_Details UHD ON MT.UserID = UHD.UserID
WHERE UHD.ParentUserID IN (SELECT ParentUserID FROM User_Heirarchy_Details WHERE UserID = 38)
  -- 往前推5个月的第一天到下月第一天（如5月则包含1-5月）
  AND MT.CreatedDate >= DATEADD(MONTH, -5, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
  AND MT.CreatedDate < DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
  AND MT.[Status] IN ('SUCCESS', 'PENDING')

滚动3/6个月（按天计算，非自然月）

如果需要按"过去90天/180天"的滚动范围查询，替换日期条件为：

-- 滚动3个月（过去90天）
AND MT.CreatedDate >= DATEADD(DAY, -90, GETDATE())

-- 滚动6个月（过去180天）
AND MT.CreatedDate >= DATEADD(DAY, -180, GETDATE())

验证步骤 #

如果仍无结果，可分步排查：

1. 单独查询Transactions表，验证日期范围内是否存在符合状态的数据：

SELECT COUNT(*), SUM(RequestAmount)
FROM Transactions
WHERE CreatedDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
  AND CreatedDate < DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
  AND [Status] IN ('SUCCESS', 'PENDING')

2. 若上述查询有数据，再验证关联条件是否过滤了目标用户层级的数据：

SELECT DISTINCT MT.UserID
FROM Transactions MT
INNER JOIN User_Heirarchy_Details UHD ON MT.UserID = UHD.UserID
WHERE UHD.ParentUserID IN (SELECT ParentUserID FROM User_Heirarchy_Details WHERE UserID = 38)

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