Server: CASE语句，在两个日期之间

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;

• 示例db<>fiddle

备注：

• 我试着猜到，如果你不知道范围结束后一个月的阅读日期，只需加一个月。
• 不要使用FORMAT，这是绝对可怕的(参1，参2，参3)。
• 可能不在您的控制范围内，但是DateTime是一个糟糕的列名选择，因为它很模糊，而且它与数据类型的名称发生冲突。