我有一組時間序列日期，我需要能夠按日期范圍求和。問題是日期范圍不是固定的，它每個月都會有一點變化。我知道提前幾個月，所以這不是問題。我很難弄清楚如何將開始日期和結束日期與選擇范圍相匹配。我可能很快就會想出一個古怪的方法，但我想尋求幫助。我的餛飩現在煮好了。
哦，我可以手動撰寫它，但這既不好玩也不靈活。
這是我的手動方法。
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 的中間時段。
謝謝一堆。
uj5u.com熱心網友回復：
你可以試試這個：
; -- 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是一個糟糕的列名選擇，因為它既含糊又因為它與資料型別的名稱沖突。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/478139.html