I'm trying do a simple average of the previous two rows. The complicated piece is if a row contains NULL in the score column, I want to do the average of the last 2 NON NULL rows.
Ordering by not null does not work as it puts the current row with the rest of the non null rows. Similar, creating a separate non null row number created the same problem. I assume I have to resolve through an outer join but just checking if anyone is aware of a simpler adjustment to the below code).
Example Data:
DECLARE @tbl TABLE
(
Team varchar(1),
date date,
Score int
);
INSERT INTO @tbl (Team, Date, Score)
VALUES
('a', '2020/12/05', null),
('a', '2020/12/04', null),
('a', '2020/12/03', null),
('a', '2020/12/02', null),
('a', '2020/11/04', '2'),
('a', '2020/10/03', '4'),
('a', '2020/08/02', '6'),
('a', '2020/06/01', '8');
SELECT
date,
avg(SCORE+0.0) OVER (partition by Team ORDER BY Date ASC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS Average
FROM
@tbl
ORDER BY
Date DESC
Output:
+--------+------------+------------+
| Row | Current | Required |
+--------+------------+------------+
| 1 | NULL | 3 |
| 2 | NULL | 3 |
| 3 | 2 | 3 |
| 4 | 3 | 3 |
| 5 | 5 | 5 |
| 6 | 7 | 7 |
| 7 | 8 | 8 |
| 8 | NULL | NULL |
+--------+------------+------------+
You can use a subquery with a top clause:
SELECT
date,
(
SELECT AVG(score+0.0)
FROM
(
SELECT TOP(2) score
FROM @tbl t2
WHERE t2.date < t1.date
AND t2.score IS NOT NULL
ORDER BY t2.date DESC
) two_previous
) as average
FROM @tbl t1
ORDER BY date DESC;
Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5ad91643b0fa21fe8a78b78ded1ce498