I need to count [SALES] for last 12 months per each row of [selected YearMonth]. For example, if 202110 is selected, I know between 202110 and 202011 I had one sale and [HAD SALES] in front of 202110 should show 1, however, if 202008 selected and I look back the last 12 months, there is no sale and my row should show 0.

I need something like below statement without having where clause or parameter in my statement, but I'm not sure how to put it together.

SELECT 
    [SELECTED YEARMONTH], 
    (COUNT(SALES) FROM TABLE 
     WHERE [SELECTED YEARMONTH] BETWEEN [SELECTED PREVYEAR] AND [SELECTED YEARMONTH])
FROM 
    TABLE;

CodePudding user response：

Try the statement below.

Note, that you should use RANGE and not ROWS inside the OVER clause, if there is a chance, that there is no row for some month in your table as in the example. The expression used in ORDER BY returns continuous enumeration for months, which is necessary for our algorithm with RANGE.

If there are no gaps in months, then you may use ORDER BY YEARMONTH ROWS BETWEEN 11 PRECEDING AND CURRENT ROW instead.

SELECT 
  YEARMONTH
, SUM (SALES) OVER 
(
ORDER BY YEARMONTH / 100 * 12   MOD (YEARMONTH, 100) 
RANGE BETWEEN 11 PRECEDING AND CURRENT ROW 
) AS SALES
FROM 
(
VALUES
  (202110, 1)
--, (202109, 1)
, (202108, 1)
, (202107, 1)
, (202106, 1)
, (202105, 1)
, (202104, 1)
, (202103, 1)
, (202102, 1)
, (202101, 1)
, (202012, 1)
, (202011, 1)
, (202010, 1)
, (202009, 1)
, (202008, 1)
) T (YEARMONTH, SALES)
ORDER BY YEARMONTH DESC