For each row in the below example data set, the code does a sum of the previous 5 rows when a certain condition is met.
The problem I'm having is the condition needs to reference the original row rating e.g. I need to sum preceding rows only if the rating is within 1 of the current row.
Example data:
DECLARE @tbl TABLE
(
Team varchar(1),
date date,
Rating int,
Score int
);
INSERT INTO @tbl (Team, Date, Rating, Score)
VALUES
('a', '2020/12/05', '20', '1'),
('a', '2020/12/04', '18', '8'),
('a', '2020/12/03', '21', '3'),
('a', '2020/12/02', '19', '4'),
('a', '2020/12/01', '19', '3');
Current code:
SELECT
Rating,
SUM(CASE WHEN Rating >= (Rating-1) AND Rating <= (Rating+1) THEN SCORE END)
OVER (partition by Team ORDER BY Date ASC ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS SUM
FROM
@tbl
ORDER BY
Date DESC
Output:
+------------------+------------+------------+
| Rating | Current | Required |
+------------------+------------+------------+
| 20 | 18 | 7 |
| 18 | 10 | 7 |
| 21 | 7 | NULL |
| 19 | 3 | 3 |
| 18 | NULL | NULL |
+------------------+------------+------------+
The problem is this following section of the code is not working as the rating is being assessed on a line by line by line basis.
CASE WHEN Rating >= (Rating-1) AND Rating <= (Rating+1)
I need it to assess against the rating of the original row (I've looked into Top but that isn't working):
CASE WHEN Rating >= ((SELECT TOP 1 Rating) - 1) AND Rating <= ((SELECT TOP 1 Rating) + 1)
Any help appreciated as always.
What you are describing sounds like a lateral join:
SELECT t.*, t2.*
FROM @tbl t OUTER APPLY
(SELECT SUM(t2.score) as score_5
FROM (SELECT TOP (5) t2.*
FROM @tbl t2
WHERE t2.date < t.date
ORDER BY t2.date DESC
) t2
WHERE t2.rating BETWEEN t.rating - 1 AND t.rating + 1
) t2
ORDER BY Date DESC