I have a set of time series dates that I need to be able to sum by a date range. The problem is the date range isn't fixed, it changes a little month to month. I know the months beforehand so that's not a problem. I'm having a hard time wrapping my head around how to match the start date and end date to the select range. I might come up with a quirky method soon but I want to ask for help. My noddle is cooked right now.

Oh, and I can write it manually, but that's no fun and not flexible.

Here is my manually method.

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]

This is the in-between periods I want to match between and spit out bMonth.

Thank you a bunch.

CodePudding user response：

You can try this one:

; -- 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;

• Example db<>fiddle

Notes:

• I went out on a limb and guessed that if you don't know the reading date for the month after the end of the range, just add a month.
• Don't use FORMAT, it's absolutely awful (ref 1, ref 2, ref 3).
• Probably not in your control, but DateTime is a poor column name choice both because it is vague and because it collides with the name of a data type.