SQL Server：案例语句，在两个日期之间

Question

我有一组时间序列日期，我需要能够按日期范围汇总。问题是日期范围不是固定的，它会更改一个月。我事先知道几个月，所以这不是问题。我很难将自己的头缠绕如何将开始日期和结束日期与选择范围匹配。我可能很快就想出了一种古怪的方法，但我想寻求帮助。我的点头正在煮熟。

哦，我可以手动写它，但这没什么好玩，也不灵活。

这是我的手动方法。

SELECT 
    [DateTime], [KWH],
    CASE 
        WHEN DateTime >= '2022-01-20' AND DateTime < '2022-02-21' 
            THEN '2022-02' 
        WHEN DateTime >= '2022-02-21' AND DateTime < '2022-03-21' 
            THEN '2022-03' 
        WHEN DateTime >= '2022-03-21' AND DateTime < '2022-04-20' 
            THEN '2022-04' 
        WHEN DateTime >= '2022-04-20' AND DateTime < '2022-05-20' 
            THEN '2022-05' 
        WHEN DateTime >= '2022-05-20' AND DateTime < '2022-06-20' 
            THEN '2022-06' 
        WHEN DateTime >= '2022-06-20' AND DateTime < '2022-07-21' 
            THEN '2022-07' 
        WHEN DateTime >= '2022-07-21' AND DateTime < '2022-08-22' 
            THEN '2022-08' 
        WHEN DateTime >= '2022-08-22' AND DateTime < '2022-09-20' 
            THEN '2022-09' 
        WHEN DateTime >= '2022-09-20' AND DateTime < '2022-10-20' 
            THEN '2022-10' 
        WHEN DateTime >= '2022-10-20' AND DateTime < '2022-11-20' 
            THEN '2022-11' 
        WHEN DateTime >= '2022-11-20' AND DateTime < '2022-12-20' 
            THEN '2022-12' 
        WHEN DateTime >= '2022-12-20' AND DateTime < '2023-01-20' 
            THEN '2023-01' 
        ELSE 'NG'
    END AS [c_Month]
FROM
    [MV90].[dbo].[someplace]

这是我想匹配和吐出bmonth之间的中间周期。

谢谢你。

Answer 1

您可以尝试此操作：

; -- see sqlblog.org/cte
WITH d AS
(
  -- from your reference / dimension table of ReadDates,
  -- grab the current row and either the next row or a
  -- month later when there is no next row
  SELECT 
    s = readDate,
    e = COALESCE(LEAD(readDate,1) OVER (ORDER BY readDate),
         DATEADD(MONTH, 1, readDate))
  FROM dbo.ReadDates -- WHERE Cycle = '22'
),
bounds AS
(
  -- from that set, build date boundaries
  -- this additional CTE is only useful in
  -- that it prevents repeating expressions
  SELECT s_readDate = CONVERT(date, s), 
         e_readDate = CONVERT(date, e), 
         bMonth     = CONVERT(char(7), e, 120)
  FROM d
)
SELECT [DateTime] = CONVERT(date, s.[DateTime]),
  s.KWH,
  b.bMonth
  -- now that we know our bounds, grab any
  -- rows from the fact table that are 
  -- inside our bounds. This is your CASE
  -- expression, without the hard-coding.
FROM bounds AS b
INNER JOIN dbo.someplace AS s
 ON s.[DateTime] >= b.s_readDate
AND s.[DateTime] <  b.e_readDate;

• 示例

BED0B0B0EBA1

• ”在肢体上，猜测，如果您不知道该范围结束后的月份的阅读日期，那就增加一个月。
• 不要使用格式，绝对很糟糕（ ref 3 ）。
• 可能不在您的控制中，但是dateTime是一个糟糕的列名选择，因为它含糊不清，并且因为它与数据类型的名称相撞。